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
x
declare @tmp as table ( [Case] int, [Name] varchar(20), [StartDate] date, [EndDate] date)insert into @tmpvalues(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) MonthListFROM ( 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 )tmpWHERE Nbr - 1 <= DATEDIFF(DAY, tmp.StartDate, tmp.EndDate)order bytmp.[Case],tmp.[Name],Detail_Dateoutput:
Case Name MonthList1 ABC 2021-01-151 ABC 2021-01-161 ABC 2021-01-171 ABC 2021-01-181 ABC 2021-01-191 ABC 2021-01-201 ABC 2021-01-211 ABC 2021-01-221 ABC 2021-01-231 ABC 2021-01-241 ABC 2021-01-251 ABC 2021-01-261 ABC 2021-01-271 ABC 2021-01-281 ABC 2021-01-291 ABC 2021-01-301 ABC 2021-01-311 ABC 2021-02-011 ABC 2021-02-021 ABC 2021-02-031 ABC 2021-02-041 ABC 2021-02-051 ABC 2021-02-061 ABC 2021-02-071 ABC 2021-02-081 ABC 2021-02-091 ABC 2021-02-101 ABC 2021-02-111 ABC 2021-02-121 ABC 2021-02-131 ABC 2021-02-141 ABC 2021-02-151 ABC 2021-02-161 ABC 2021-02-171 ABC 2021-02-181 ABC 2021-02-191 ABC 2021-02-201 ABC 2021-02-211 ABC 2021-02-221 ABC 2021-02-231 ABC 2021-02-241 ABC 2021-02-251 ABC 2021-02-261 ABC 2021-02-271 ABC 2021-02-281 ABC 2021-03-011 ABC 2021-03-021 ABC 2021-03-031 ABC 2021-03-041 ABC 2021-03-051 ABC 2021-03-061 ABC 2021-03-071 ABC 2021-03-081 ABC 2021-03-091 ABC 2021-03-101 ABC 2021-03-111 ABC 2021-03-121 ABC 2021-03-131 ABC 2021-03-141 ABC 2021-03-152 DEF 2021-03-152 DEF 2021-03-162 DEF 2021-03-172 DEF 2021-03-182 DEF 2021-03-192 DEF 2021-03-202 DEF 2021-03-212 DEF 2021-03-222 DEF 2021-03-232 DEF 2021-03-242 DEF 2021-03-252 DEF 2021-03-262 DEF 2021-03-272 DEF 2021-03-282 DEF 2021-03-292 DEF 2021-03-302 DEF 2021-03-312 DEF 2021-04-012 DEF 2021-04-022 DEF 2021-04-032 DEF 2021-04-042 DEF 2021-04-052 DEF 2021-04-062 DEF 2021-04-072 DEF 2021-04-082 DEF 2021-04-092 DEF 2021-04-102 DEF 2021-04-112 DEF 2021-04-122 DEF 2021-04-132 DEF 2021-04-142 DEF 2021-04-152 DEF 2021-04-162 DEF 2021-04-172 DEF 2021-04-182 DEF 2021-04-192 DEF 2021-04-202 DEF 2021-04-212 DEF 2021-04-222 DEF 2021-04-232 DEF 2021-04-242 DEF 2021-04-252 DEF 2021-04-262 DEF 2021-04-272 DEF 2021-04-282 DEF 2021-04-292 DEF 2021-04-302 DEF 2021-05-012 DEF 2021-05-022 DEF 2021-05-032 DEF 2021-05-042 DEF 2021-05-052 DEF 2021-05-062 DEF 2021-05-072 DEF 2021-05-082 DEF 2021-05-092 DEF 2021-05-102 DEF 2021-05-112 DEF 2021-05-122 DEF 2021-05-132 DEF 2021-05-142 DEF 2021-05-15