Split date range from a single row into months that are displayed in multiple rows

Question

I have a table with a few million entries, so I need a solution that can work on a bunch of different scenarios as most solutions I have found is only for a few iterations at best.

I have a PERIOD_FROM and a PERIOD_TO status check, aka how long the account has been in this state. It is no problem to split a two month difference but quite a few of the entries are for accounts that have paid in advance so their status will not change for up to 2 years.

The original table simplified looks something like this:

ID          PERIOD_FROM PERIOD_TO   DAYS
---------------------------------------------------------
115052454   02/04/2019  01/04/2021  730
115678935   06/04/2021  05/04/2023  729
119040627   06/04/2021  05/04/2023  729
115005487   01/04/2019  29/03/2021  728
116414279   17/04/2019  09/04/2021  723
116411046   17/04/2019  04/04/2021  718
116693271   24/04/2019  04/04/2021  711
123935704   05/07/2019  29/05/2021  694
119040627   16/05/2019  05/04/2021  690
115976183   02/05/2019  01/03/2021  669

What I have done for the two month difference solution is create a concat as follows:

CASE WHEN CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM')) = CONCAT(DATEPART(YYYY, [PERIOD_TO]), FORMAT([PERIOD_TO], 'MM'))
    THEN CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM'))
        ELSE CONCAT(DATEPART(YYYY, [PERIOD_FROM]), FORMAT([PERIOD_FROM], 'MM'), ',', DATEPART(YYYY, [PERIOD_TO]), FORMAT([PERIOD_TO], 'MM'))
END AS Period_Of

Using cross apply with string_split I can then split the line into two and using another case statement I can assign the full values based on if the period from/to is equal or greater than the split value, but this only works for a two month difference and I need up to 48 months.

I require an output that would look something like this:

ID          PERIOD_FROM PERIOD_TO   DAYS
------------------------------------------------------
1150524545  02/04/2019  30/04/2019  730
1150524545  01/05/2019  31/05/2019  730
1150524545  01/06/2019  30/06/2019  730
1150524545  01/07/2019  31/07/2019  730
   …………            …………             …………    ……
1150524545  01/02/2021  28/02/2021  730
1150524545  01/03/2021  31/03/2021  730
1150524545  01/04/2021  01/04/2021  730

Any help would be greatly appreciated!

UPDATE:

Thanks to the help of Serg, I have a solution. I tweaked it slightly so that the Period_To ends on the last date but had not specified that in my request as it was not a main concern.

Possible duplicate of [SQL Date Range Split](https://stackoverflow.com/questions/185110/sql-date-range-split) — Serg, Sep 20 '19 at 06:50
I am working on SQL Server 2017, and I did try to implement the link you suggested and it did not solve my problem it just split my row into two, where there should be 48, and proceeded to try to give me a date after my period had ended which does not help me. — McAwesome, Sep 20 '19 at 07:29
You might want to familarize yourself with how this site works. It doesn't work by editing your title to put `(SOLVED)` in. [What should I do when someone answers my question?](https://stackoverflow.com/help/someone-answers), and, if necessary, [Can I answer my own question?](https://stackoverflow.com/help/self-answer) — Damien_The_Unbeliever, Sep 20 '19 at 08:43

score 0 · Accepted Answer · answered Sep 20 '19 at 08:36

Using table of numbers

-- Generate table of 1000 numbers starting 0
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),nmbs as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2, t0 t3
)
--
select Id
  , dateadd(mm, nmbs.n, tbl.PERIOD_FROM) PERIOD_FROM
  , case when ys.NextDate > tbl.PERIOD_TO then tbl.PERIOD_TO else ys.NextDate end PERIOD_TO
  , DAYS
  , tbl.PERIOD_TO originalPERIOD_TO
from [my table] tbl
join nmbs
on dateadd(mm, nmbs.n, tbl.PERIOD_FROM) <= tbl.PERIOD_TO
cross apply (select  dateadd(mm, nmbs.n + 1, tbl.PERIOD_FROM) NextDate) ys
order by ID, dateadd(mm, nmbs.n, tbl.PERIOD_FROM);

Fiddle

Split date range from a single row into months that are displayed in multiple rows

1 Answers1

Linked