Skip to content
Advertisement

Split multi-month records into individual months

I have data in a table in this format – where date range is multi-month:

SourceSink  Class   ShadowPrice Round   Period  StartDate   EndDate
 AEC        Peak    447.038      3     WIN2020  2020-12-01  2021-02-28

I want to create a view/ insert into a new table – the above record broken by month as shown below:

SourceSink  Class   ShadowPrice Round   Period  StartDate   EndDate
 AEC        Peak    447.038      3     WIN2020  2020-12-01  2021-12-31
 AEC        Peak    447.038      3     WIN2020  2021-01-01  2021-01-31
 AEC        Peak    447.038      3     WIN2020  2021-02-01  2021-02-28

Please advise.

Advertisement

Answer

Just another option using a CROSS APPLY and an ad-hoc tally table

Example

Select A.[SourceSink]
      ,A.[Class]
      ,A.[ShadowPrice]
      ,A.[Round]
      ,A.[Period]
      ,B.[StartDate]
      ,B.[EndDate]
 From YourTable A
 Cross Apply (
                Select StartDate=min(D)
                      ,EndDate  =max(D)
                  From (
                         Select Top (DateDiff(DAY,[StartDate],[EndDate])+1) 
                                D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),[StartDate]) 
                          From  master..spt_values n1,master..spt_values n2
                       ) B1
                  Group By Year(D),Month(D)
             ) B

Returns

enter image description here

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement