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 |
Advertisement
Answer
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