How to convert a date range table to a row based view?

Question

I’m facing a tough situation. I could not find a solution so far.

I have a table giving information based on date ranges. I’d like to have this information broken down by date. So I’m looking to convert the range into a row structure.

The extra difficulty is that the number of “periods” in the date range is variable. The "periodicity" is deducted by the date range and the number of days in one period.

To be more specific, on one line of the table I've an

ID

start_date of the range

end_date of the range

number of days_in_the_period

numbers_periods

pricings to apply to each period in the range

Here is the initial table structure and the expected result:

CREATE TABLE Start(
 Key                VARCHAR(11) NOT NULL PRIMARY KEY
,Start_date         VARCHAR(27) NOT NULL
,End_Date           VARCHAR(27) NOT NULL
,Days_in_the_period INTEGER  NOT NULL
,Nbr_periods        INTEGER  NOT NULL
,Pricing            VARCHAR(6) NOT NULL
);
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2000-06-01 00:00:00.0000000','2001-12-01 00:00:00.0000000',30,19,'800,87');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2002-01-01 00:00:00.0000000','2005-12-01 00:00:00.0000000',30,48,'440,32');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2006-01-01 00:00:00.0000000','2007-02-01 00:00:00.0000000',30,14,'282,68');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2007-03-01 00:00:00.0000000','2008-03-01 00:00:00.0000000',30,13,'283,99');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2008-04-01 00:00:00.0000000','2009-01-01 00:00:00.0000000',60,5,'281,81');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2009-02-01 00:00:00.0000000','2009-03-01 00:00:00.0000000',30,2,'281,81');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2009-04-01 00:00:00.0000000','2019-07-01 00:00:00.0000000',30,124,'281,81');
INSERT INTO Start(Key,Start_date,End_Date,Days_in_the_period,Nbr_periods,Pricing) VALUES ('010-1280001','2019-08-01 00:00:00.0000000','2019-08-01 00:00:00.0000000',0,1,'372,96');

Expected

  Key                  Date               Pricing   Days_in_the_period

010-1280001 2000-06-01 00:00:00.0000000 800,87 30
010-1280001 2000-07-01 00:00:00.0000000 800,87 30
… … … …
010-1280001 2008-04-01 00:00:00.0000000 281,81 60
010-1280001 2008-06-01 00:00:00.0000000 281,81 60
… … … …
010-1280001 2019-08-01 00:00:00.0000000 372,96 0

For information, the initial table contains about 100k records. Does anyone has a brilliant idea for me?

Please revert for any clarification, Tartino.

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, Sep 26 '16 at 13:57
Can you explain the logic on how start and end date becomes as single date column in Expected results? — Kannan Kandasamy, Sep 26 '16 at 13:57
Dear Kannan, The start date of the range becomes the first date in the single date. Then based on the number of days in the period, the second single date is calculated. (30 days will be rounded to a month) It continues as many times as there are periods (or till then end date) — Tartino, Sep 26 '16 at 14:03

gofr1 · Accepted Answer · 2016-09-27T07:51:38.777

0

You can do this with the help of recursive CTE:

;WITH cte AS (
    SELECT  *
    FROM YourTable
    UNION ALL
    SELECT  c.[key],
            DATEADD(month,c.Days_in_the_period/30,c.[Start_Date]),
            c.End_Date,
            c.Days_in_the_period,
            c.Nbr_periods,
            c.Pricing
    FROM cte c
    INNER JOIN YourTable y
        ON y.[key] = c.[key] AND c.End_Date = y.End_Date
    WHERE y.End_Date >=DATEADD(month,c.Days_in_the_period/30,c.[Start_Date])
)


SELECT  [key],
        [Start_Date] as [Date],
        Pricing,
        Days_in_the_period
FROM cte
ORDER BY [key], [Start_Date]

Another way is to use calendar table, and join it with your table.

edited Sep 27 '16 at 07:51

answered Sep 26 '16 at 14:52

gofr1

15,741
11
42
52

Thanks Gofr, your structure came in very handy. I had to change some little things to avoid infinite recursive but it's now working fine. – Tartino Sep 27 '16 at 12:48
This code worked for sample of data you provided (I used two first rows). Use `OPTION (MAXRECURSION 0)` if there are intervals more then 100 days - forgot to mention it in my answer. – gofr1 Sep 27 '16 at 12:51
Great to hear this! If my answer was helpful be free to accept/upvote it! – gofr1 Sep 27 '16 at 12:54
The last row had a days in period of 0 and would generate in infinte loop since 0/30 + any = any. But it's only a detail. Thanks!! – Tartino Sep 27 '16 at 14:16

How to convert a date range table to a row based view?

1 Answers1