In an earlier post, I provided an example of how to use a UDF that returns calendar days in order to generate data from a table valued function. Today, I expand on this using a similar technique, but based on an interval range table which can be used generically. If you've been following my blog, then you're aware of my interest in simulations. The technique that I am going over today is great for this scenario, as we can generate all sorts of combinations of parameters into a result set which can then be joined to our sources to generate the desired combinations of data to test.
To start with, we create a table for storing information about the ranges and how we would like to generate them:
CREATE TABLE [Olap].[IntervalRange](
[RangeId] [int] IDENTITY(1,1) NOT NULL,
[RangeStart] [smallmoney] NOT NULL,
[RangeEnd] [smallmoney] NOT NULL,
[RangeType] [varchar](5) NOT NULL,
[RangeInterval] [smallmoney] NOT NULL,
[RangeGrade] [tinyint] NULL,
CONSTRAINT [PK_Range] PRIMARY KEY CLUSTERED
(
[RangeId] ASC
)) ON [PRIMARY]
Below is some sample data. The RangeStart
indicates the first number in the range, the RangeEnd
indicates the ending value and the RangeInterval
determines the increment to use when generating the range.
RangeId | RangeStart | RangeEnd | RangeType | RangeInterval | RangeGrade |
3 | -6 | 21 | ENTRY | 3 | NULL |
4 | 25 | 40 | ENTRY | 5 | NULL |
6 | 2 | 10 | EXIT | 2 | NULL |
7 | 13 | 25 | EXIT | 3 | NULL |
16 | 1 | 5 | EPS | 1 | NULL |
From this, we can easily join the data and then insert into a table that contains the combinations to generate a list of strategies which include the EPS Grade, the Exit divisor, and the entry threshold:
CREATE PROCEDURE [Olap].[GenerateStrategies]
AS BEGIN
INSERT INTO Olap.Strategy ( EPSGrade, EntryThreshold, ExitDivisor)
SELECT eps.Interval as EPSGrade,
e.Interval as EntryThreshold,
x.Interval as ExitDivisor
FROM Olap.view_RangeIntervals eps
CROSS JOIN Olap.view_RangeIntervals e
CROSS JOIN Olap.view_RangeIntervals x
WHERE eps.RangeType = 'EPS'
AND e.RangeType = 'ENTRY'
AND x.RangeType = 'EXIT'
AND NOT EXISTS (SELECT 0 FROM Olap.Strategy
WHERE e.Interval = EntryThreshold
AND x.Interval = ExitDivisor
AND eps.Interval = EPSGrade)
END
Here are the first few results and the last few results of the cross joins of the parameters. I won't show all of them because it turns out that the select
query above actually generates 700 rows in total. This is because there are 5 EPS intervals in total, 14 Entry intervals, and 10 Exit intervals, which works out to 5 X 14 X 10 or 700. So, we basically generate 700 different combinations from our 5 row interval range blueprint data.
RangeType | Interval | RangeType | Interval | RangeType | Interval |
EPS | 1 | ENTRY | -6 | EXIT | 2 |
EPS | 1 | ENTRY | -3 | EXIT | 2 |
EPS | 1 | ENTRY | 0 | EXIT | 2 |
EPS | 1 | ENTRY | 3 | EXIT | 2 |
EPS | 1 | ENTRY | 6 | EXIT | 2 |
EPS | 1 | ENTRY | 9 | EXIT | 2 |
EPS | 1 | ENTRY | 12 | EXIT | 2 |
EPS | 1 | ENTRY | 15 | EXIT | 2 |
EPS | 1 | ENTRY | 18 | EXIT | 2 |
EPS | 1 | ENTRY | 21 | EXIT | 2 |
EPS | 1 | ENTRY | 25 | EXIT | 2 |
EPS | 1 | ENTRY | 30 | EXIT | 2 |
EPS | 1 | ENTRY | 35 | EXIT | 2 |
EPS | 1 | ENTRY | 40 | EXIT | 2 |
EPS | 1 | ENTRY | -6 | EXIT | 4 |
EPS | 1 | ENTRY | -3 | EXIT | 4 |
EPS | 1 | ENTRY | 0 | EXIT | 4 |
EPS | 1 | ENTRY | 3 | EXIT | 4 |
EPS | 1 | ENTRY | 6 | EXIT | 4 |
EPS | ... | ENTRY | ... | ... | ... |
EPS | 5 | ENTRY | 35 | EXIT | 22 |
EPS | 5 | ENTRY | 40 | EXIT | 22 |
EPS | 5 | ENTRY | -6 | EXIT | 25 |
EPS | 5 | ENTRY | -3 | EXIT | 25 |
EPS | 5 | ENTRY | 0 | EXIT | 25 |
EPS | 5 | ENTRY | 3 | EXIT | 25 |
EPS | 5 | ENTRY | 6 | EXIT | 25 |
EPS | 5 | ENTRY | 9 | EXIT | 25 |
EPS | 5 | ENTRY | 12 | EXIT | 25 |
EPS | 5 | ENTRY | 15 | EXIT | 25 |
EPS | 5 | ENTRY | 18 | EXIT | 25 |
EPS | 5 | ENTRY | 21 | EXIT | 25 |
EPS | 5 | ENTRY | 25 | EXIT | 25 |
EPS | 5 | ENTRY | 30 | EXIT | 25 |
EPS | 5 | ENTRY | 35 | EXIT | 25 |
EPS | 5 | ENTRY | 40 | EXIT | 25 |
There you have it, a whole bunch of permutations from just a small amount of definition data. One caveat is that you may end up with some combinations that are at an extreme and don't make sense for your simulation. To get around this issue, I use an exclusion table that gets generated based on ineffective strategy combinations. This then gets fed back into the process such that strategy permutations are linked via a left outer join, such that those which are found in the exclusion list do not get included in the generation routine.