Created
February 24, 2023 21:52
-
-
Save jingyang-li/5007f2b0ee82bd56b4a93c7c65e7762f to your computer and use it in GitHub Desktop.
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment