I have a problem that I am trying to solve with t-sql but I cant figure it out by myself.

I have a simple query:

select StartDate, EndDate
from ProductTable
where Site = 'X' and  Product_ID = '1'

The result can look like this (there can be one or more rows with start and end dates):

What I want to do then is that for each row in this resultset, I want to create a list of months between the dates, on the format "yyyymm", and then union the result of these lists to one resultset.

So for the 3 rows in the first resultset the first step should give:

ROW 1: 201906, 201907, 201908, 201909

ROW 2: 201912, 202001, 202002, 202003, 202004

ROW 3: 202011, 202012

And the final expected result is then of course:

I have experimented a bit with CTEs and Cursors but I haven't real had any success yet.

Can someone help me out? :-)

CodePudding user response：

A recursive CTE works well to unfold date ranges.

;WITH RCTE_DATES AS (
   SELECT 
     DATEADD(month, -1, DATEADD(day, 1, EOMONTH(StartDate))) AS StartDate
   , DATEADD(month, -1, DATEADD(day, 1, EOMONTH(EndDate))) AS EndDate
   FROM ProductTable
   WHERE Site = 'X' AND Product_ID = '1'

   UNION ALL
   
   SELECT DATEADD(month, 1, StartDate), EndDate
   FROM RCTE_DATES
   WHERE StartDate < EndDate
)
, CTE_YEARMONTHS AS (
  SELECT DISTINCT
    YEAR(StartDate)*100 MONTH(StartDate) AS YearMonth
  FROM RCTE_DATES
) 
SELECT *
FROM CTE_YEARMONTHS
ORDER BY YearMonth;

| YearMonth |
| --------: |
|    201906 |
|    201907 |
|    201908 |
|    201909 |
|    201912 |
|    202001 |
|    202002 |
|    202003 |
|    202004 |
|    202011 |
|    202012 |

Test on db<>fiddle

;WITH m(m) AS 
(
  SELECT TOP (256) m = ROW_NUMBER() OVER (ORDER BY [object_id])-1
    FROM sys.all_objects ORDER BY [object_id]
)
SELECT CONVERT(char(6), DATEADD(MONTH, m.m, p.StartDate), 112)
  FROM dbo.ProductTable AS p
  INNER JOIN m ON m.m <= DATEDIFF(MONTH, p.StartDate, p.EndDate)
  WHERE Product_ID = '1' AND Site = 'X';