Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / SQL

A Generic Interval Range Table to Generate Permutations

5.00/5 (1 vote)
4 Aug 2009CPOL2 min read 9.9K  
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:

SQL
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.

RangeIdRangeStartRangeEndRangeTypeRangeIntervalRangeGrade
3-621ENTRY3NULL
42540ENTRY5NULL
6210EXIT2NULL
71325EXIT3NULL
1615EPS1NULL

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:

SQL
    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.

RangeTypeIntervalRangeTypeIntervalRangeTypeInterval
EPS1ENTRY-6EXIT2
EPS1ENTRY-3EXIT2
EPS1ENTRY0EXIT2
EPS1ENTRY3EXIT2
EPS1ENTRY6EXIT2
EPS1ENTRY9EXIT2
EPS1ENTRY12EXIT2
EPS1ENTRY15EXIT2
EPS1ENTRY18EXIT2
EPS1ENTRY21EXIT2
EPS1ENTRY25EXIT2
EPS1ENTRY30EXIT2
EPS1ENTRY35EXIT2
EPS1ENTRY40EXIT2
EPS1ENTRY-6EXIT4
EPS1ENTRY-3EXIT4
EPS1ENTRY0EXIT4
EPS1ENTRY3EXIT4
EPS1ENTRY6EXIT4
EPS...ENTRY.........
EPS5ENTRY35EXIT22
EPS5ENTRY40EXIT22
EPS5ENTRY-6EXIT25
EPS5ENTRY-3EXIT25
EPS5ENTRY0EXIT25
EPS5ENTRY3EXIT25
EPS5ENTRY6EXIT25
EPS5ENTRY9EXIT25
EPS5ENTRY12EXIT25
EPS5ENTRY15EXIT25
EPS5ENTRY18EXIT25
EPS5ENTRY21EXIT25
EPS5ENTRY25EXIT25
EPS5ENTRY30EXIT25
EPS5ENTRY35EXIT25
EPS5ENTRY40EXIT25

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)