Expand Date Range

Posted: February 24, 2023 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

	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

Jingyang Li