A Generic Interval Range Table to Generate Permutations

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.