SQL Server - Retrieve list of month dates between two columns

Question

I want to add a new column that should contain months between startdate & enddate present in two separate columns.

My current data looks something like this :

Case	Name	StartDate	EndDate
1	ABC	2021-01-15	2021-03-15
2	DEF	2021-03-15	2021-05-15

My desired output is :

Case	Name	StartDate	EndDate	MonthList
1	ABC	2021-01-01	2021-03-15	2021-01-15
1	ABC	2021-01-01	2021-03-15	2021-02-15
1	ABC	2021-01-01	2021-03-15	2021-03-15
2	DEF	2021-03-01	2021-05-15	2021-03-15
2	DEF	2021-03-01	2021-05-15	2021-04-15
2	DEF	2021-03-01	2021-05-15	2021-05-15

Please make sure to include all edge cases, e.g. when the range is less than a month, or more than a year, or start and end dates aren't exactly a month apart, or either date falls on days other than the 15th. If you only show of us two of your rows, we need to make a lot of assumptions about all of the other rows, and we're much more likely to be wrong than right. — Aaron Bertrand, Nov 19 '21 at 18:21

score 2 · Answer 1 · answered Nov 19 '21 at 15:57

(Edit: Aaron's comment is a good point - I have made the exact assumptions he talks about re the data - i.e. this is all good if your dates are always 15th of each month. If that is not true, you may have some work to do, so either improve your question or work it out yourself from this starting point)

This should get you what you want

CREATE TABLE #data
(
    [Case] INT,
    Name    VARCHAR(3),
    StartDate DATE,
    EndDate DATE
);

INSERT INTO #data
VALUES
(1,'ABC','2021-01-15','2021-03-15'),
(2,'DEF','2021-03-15','2021-05-15');

WITH cte([Case], [Name], dt) AS 
(
    SELECT [Case], [Name], StartDate AS dt
    FROM #data 
    UNION ALL
    SELECT cte.[Case], cte.Name, DATEADD(MONTH, 1, dt)
    FROM cte 
    INNER JOIN #data ON #data.[Case] = cte.[Case] AND #data.[Name] = cte.[Name]
    WHERE dt < EndDate
)
SELECT *
FROM cte
ORDER BY cte.[Case], cte.Name, cte.dt

But in reality recursive queries like this are hard to maintain. I would create a "Months" table with a load of months in, then you can just join to that where month is between StartDate and EndDate:

CREATE TABLE #months -- This would be a concrete table in your db, not temp
(
    MonthDate DATE
)

INSERT INTO #months -- you would need to populate this with enough months to satisfy your needs
VALUES 
('2021-01-15'),
('2021-02-15'),
('2021-03-15'),
('2021-04-15'),
('2021-05-15'),
('2021-06-15');

SELECT d.[Case], d.[Name], m.MonthDate
FROM #data d
INNER JOIN #months m ON m.MonthDate BETWEEN d.StartDate AND d.EndDate
ORDER BY d.[Case], d.[Name], m.MonthDate

Golden Lion · Accepted Answer · 2021-11-19T20:46:42.357

see (Generate Dates between date ranges)
use a cross join to combine your tmp table with the date generator code

declare @tmp as table ( [Case] int, [Name] varchar(20), [StartDate] date, [EndDate] date)

insert into @tmp
values(1,   'ABC',  '2021-01-15',   '2021-03-15')
,(2,    'DEF'   ,'2021-03-15',  '2021-05-15')


SELECT  
tmp.[Case],
tmp.[Name],
DATEADD(DAY, Nbr - 1, tmp.StartDate) MonthList
FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY c.object_id ) AS Nbr
          FROM      sys.columns c
        ) nbrs
        cross join
        (
            select [Name],[Case],[StartDate],[EndDate] from @tmp
        )tmp
WHERE   Nbr - 1 <= DATEDIFF(DAY, tmp.StartDate, tmp.EndDate)
order by
tmp.[Case],
tmp.[Name],
Detail_Date

output:

    Case    Name    MonthList
1   ABC 2021-01-15
1   ABC 2021-01-16
1   ABC 2021-01-17
1   ABC 2021-01-18
1   ABC 2021-01-19
1   ABC 2021-01-20
1   ABC 2021-01-21
1   ABC 2021-01-22
1   ABC 2021-01-23
1   ABC 2021-01-24
1   ABC 2021-01-25
1   ABC 2021-01-26
1   ABC 2021-01-27
1   ABC 2021-01-28
1   ABC 2021-01-29
1   ABC 2021-01-30
1   ABC 2021-01-31
1   ABC 2021-02-01
1   ABC 2021-02-02
1   ABC 2021-02-03
1   ABC 2021-02-04
1   ABC 2021-02-05
1   ABC 2021-02-06
1   ABC 2021-02-07
1   ABC 2021-02-08
1   ABC 2021-02-09
1   ABC 2021-02-10
1   ABC 2021-02-11
1   ABC 2021-02-12
1   ABC 2021-02-13
1   ABC 2021-02-14
1   ABC 2021-02-15
1   ABC 2021-02-16
1   ABC 2021-02-17
1   ABC 2021-02-18
1   ABC 2021-02-19
1   ABC 2021-02-20
1   ABC 2021-02-21
1   ABC 2021-02-22
1   ABC 2021-02-23
1   ABC 2021-02-24
1   ABC 2021-02-25
1   ABC 2021-02-26
1   ABC 2021-02-27
1   ABC 2021-02-28
1   ABC 2021-03-01
1   ABC 2021-03-02
1   ABC 2021-03-03
1   ABC 2021-03-04
1   ABC 2021-03-05
1   ABC 2021-03-06
1   ABC 2021-03-07
1   ABC 2021-03-08
1   ABC 2021-03-09
1   ABC 2021-03-10
1   ABC 2021-03-11
1   ABC 2021-03-12
1   ABC 2021-03-13
1   ABC 2021-03-14
1   ABC 2021-03-15
2   DEF 2021-03-15
2   DEF 2021-03-16
2   DEF 2021-03-17
2   DEF 2021-03-18
2   DEF 2021-03-19
2   DEF 2021-03-20
2   DEF 2021-03-21
2   DEF 2021-03-22
2   DEF 2021-03-23
2   DEF 2021-03-24
2   DEF 2021-03-25
2   DEF 2021-03-26
2   DEF 2021-03-27
2   DEF 2021-03-28
2   DEF 2021-03-29
2   DEF 2021-03-30
2   DEF 2021-03-31
2   DEF 2021-04-01
2   DEF 2021-04-02
2   DEF 2021-04-03
2   DEF 2021-04-04
2   DEF 2021-04-05
2   DEF 2021-04-06
2   DEF 2021-04-07
2   DEF 2021-04-08
2   DEF 2021-04-09
2   DEF 2021-04-10
2   DEF 2021-04-11
2   DEF 2021-04-12
2   DEF 2021-04-13
2   DEF 2021-04-14
2   DEF 2021-04-15
2   DEF 2021-04-16
2   DEF 2021-04-17
2   DEF 2021-04-18
2   DEF 2021-04-19
2   DEF 2021-04-20
2   DEF 2021-04-21
2   DEF 2021-04-22
2   DEF 2021-04-23
2   DEF 2021-04-24
2   DEF 2021-04-25
2   DEF 2021-04-26
2   DEF 2021-04-27
2   DEF 2021-04-28
2   DEF 2021-04-29
2   DEF 2021-04-30
2   DEF 2021-05-01
2   DEF 2021-05-02
2   DEF 2021-05-03
2   DEF 2021-05-04
2   DEF 2021-05-05
2   DEF 2021-05-06
2   DEF 2021-05-07
2   DEF 2021-05-08
2   DEF 2021-05-09
2   DEF 2021-05-10
2   DEF 2021-05-11
2   DEF 2021-05-12
2   DEF 2021-05-13
2   DEF 2021-05-14
2   DEF 2021-05-15

SQL Server - Retrieve list of month dates between two columns

2 Answers2