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

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.

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.