Expand Date Range
Posted: February 24, 2023 | Author: Jingyang Li | Filed under: Uncategorized |Leave a comment
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE table #TBL | |
(DATE1 DATETIME | |
,ID INT | |
,Col1 Varchar(5) | |
,Col2 Varchar(5) | |
) | |
Insert into #TBL values | |
('12/1/22', 1001, 'abc', 'df') | |
,('12/5/22', 1001, 'abc', 'def') | |
,('12/5/22', 1002, 'dcb', 'ef') | |
,('12/10/22', 1003, 'cdf', 'efg') | |
,('12/10/22', 1001, 'abc', 'efg') | |
declare @startdate date | |
declare @enddate date | |
Select @startdate = min(DATE1),@enddate = max(DATE1) from #TBL | |
–**** create a Number table | |
;WITH Num1 (n) AS ( | |
SELECT 1 as n | |
UNION ALL SELECT n+1 as n | |
FROM Num1 Where n <101), | |
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1) | |
,mycte as | |
( | |
select DATE1,ID,Col1,COl2, dateadd(day,n-1,@startdate) dt ,DATE2 | |
from Nums | |
Cross apply (Select Distinct DATE1,ID,Col1,COl2 ,LEAD(DATE1) over (partition by ID,Col1 order by DATE1 ) DATE2 From #TBL ) a | |
WHERE dateadd(day,n-1,@startdate)<=@enddate | |
) | |
,mycte2 as ( | |
Select DATE1, dt, c.ID,c.Col1,c.Col2 ,DATE2 | |
FROM mycte c | |
WHERE c.dt<=@enddate –last date | |
) | |
select dt,ID,Col1,Col2 | |
from mycte2 | |
where dt>=DATE1 and (dt<DATE2 or DATE2 is null) | |
Order by 2,1 | |
drop table #TBL |