Click here to Skip to main content
Click here to Skip to main content
Technical Blog

Tagged as

A Generic Interval Range Table to Generate Permutations

, 4 Aug 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
In earlier post (http://blogs.msdn.com/microsoftbob/archive/2009/07/06/generating-a-list-of-calendar-dates.aspx), 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

In earlier post (http://blogs.msdn.com/microsoftbob/archive/2009/07/06/generating-a-list-of-calendar-dates.aspx), 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 range end indicates the ending value and the range interval 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. 

License

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

Share

About the Author

bobleith

United States United States
No Biography provided

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web04 | 2.8.141029.1 | Last Updated 4 Aug 2009
Article Copyright 2009 by bobleith
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid