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