CODEWITHSUNDEEP
sqlsql-serverdate
Tim
Tim

Reputation: 51

use Enddate to generate Months(Dates) to Enddates in SQL

I have a table as below:

|-------------------------|
|ID  | Enddate    | MRR(£)|
|-------------------------|
| 1  | 2020-05-10 | 100   |
| 2  | 2021-01-01 | 120   |
| 3  | 2020-10-10 |  50   |
| 4  | 2020-05-02 |  80   |
| 5  | 2020-12-30 | 150   |
|-------------------------|

I want to generate from the table above to return ID, Enddate, MRR and Month as shown

|------------------------------------|
|ID  | Enddate    | MRR(£)| Month    |
|------------------------------------|
| 1  | 2020-05-10 | 100   | NULL     |
| 2  | 2021-01-01 | 120   |2020-06-01|
| 2  | 2021-01-01 | 120   |2020-07-01|
| 2  | 2021-01-01 | 120   |2020-08-01|
| 2  | 2021-01-01 | 120   |2020-09-01|
| 2  | 2021-01-01 | 120   |2020-10-01|
| 2  | 2021-01-01 | 120   |2020-11-01|
| 2  | 2021-01-01 | 120   |2020-12-01|
| 2  | 2021-01-01 | 120   |2021-01-01|
| 3  | 2020-10-10 |  50   |2020-06-01|
| 3  | 2020-10-10 |  50   |2020-07-01|
| 3  | 2020-10-10 |  50   |2020-08-01|
| 3  | 2020-10-10 |  50   |2020-09-01|
| 3  | 2020-10-10 |  50   |2020-10-01|
| 4  | 2020-05-02 |  80   | NULL     |
| 5  | 2020-12-30 | 150   |2020-06-01|
| 5  | 2020-12-30 | 150   |2020-07-01|
| 5  | 2020-12-30 | 150   |2020-08-01|
| 5  | 2020-12-30 | 150   |2020-09-01|
| 5  | 2020-12-30 | 150   |2020-10-01|
| 5  | 2020-12-30 | 150   |2020-11-01|
| 5  | 2020-12-30 | 150   |2020-12-01|
|------------------------------------|

Note the Month column of example ID = 2 consist of next month up to the enddate. The same applies to ID of 3 and 5. However, ID 1 and 4 Has NULL because we are in May 2020 and it is skip because enddate didn't have future date.

I wrote a code by i didn't get it right so i needed help. 

SELECT  DATEADD(DAY, nbr - 1, /Start Date/Convert(date,Dateadd(dd,1 - DATEPART(dd,getdate()), DATEADD(mm,1,getdate())),103)) AS Dates
FROM    
( SELECT ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
  FROM sys.columns c  
 ) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, 
       /**Start Date**/
Convert(date,Dateadd(dd,1 - DATEPART(dd,getdate()), DATEADD(mm,1,getdate())),103), 
/**End Date**/
(SELECT enddate from TestTim.[dbo].[OrderTable] where enddate >= cast(getdate() as Date) and ID = 5))

This is the code I have. But it's just for ID=5 but I can't figure out how to add ID, Enddate and MRR. And it is not dynamic for other IDs.

/End Date/ and /Start Date/ shows represent the code that follow them

Upvotes: 0

Views: 36

Answers (1)

Nick
Nick

Reputation: 147266

You can use a recursive CTE to generate the list of dates, finding the first day of the next month and then adding a month until the Enddate:

WITH CTE AS (
  SELECT ID, Enddate, [MRR(£)], 
         CASE WHEN Enddate <= EOMONTH(GETDATE()) THEN NULL
              ELSE DATEADD(DAY, 1, EOMONTH(GETDATE()))
         END AS Month
  FROM data
  UNION ALL
  SELECT ID, Enddate, [MRR(£)], DATEADD(MONTH, 1, Month)
  FROM CTE
  WHERE DATEADD(MONTH, 1, Month) <= Enddate
)
SELECT *
FROM CTE
ORDER BY ID, Month

Output is too long to show here but as desired. Demo on SQLFiddle

Upvotes: 1

Related Questions