SQL Server - Retrieve list of month dates between two columns-CodePudding

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

CodePudding user response：

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) Detail_Date
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    Detail_Date
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

CodePudding user response：

(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