0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for enthusiasts and power users of the Android operating system

DECLARE @HorizonStartDate date = '2019-12-30';
DECLARE @HorizonEndDate date = '2020-12-31';

WITH CTE AS
(
 SELECT DATEADD(dd, 0, '2020-01-01') BucketDate, 'F' as BucketIndicator
 UNION ALL
 SELECT  BucketDate + 1, 'T' as BucketIndicator
 FROM    CTE   
 WHERE   BucketDate + 1 <= '2020-12-31'
),
CTE1 AS(
select c.*, MIN(c.BucketDate) OVER(PARTITION by c.BucketIndicator) as HorizonDate
from CTE c where c.BucketIndicator ='F'
UNION
select c.*, MAX(c.BucketDate) OVER(PARTITION by c.BucketIndicator) as HorizonDate
from CTE c where c.BucketIndicator ='T'

),

CTE2 AS(
select c1.*, CASE when (c1.BucketDate> c1.HorizonDate) then DATEADD(day,-1,c1.BucketDate)
else c1.BucketDate
end as NewBucketDate 
from CTE1 c1
where c1.BucketIndicator='F'
UNION
select c1.*, CASE when (c1.BucketDate< c1.HorizonDate ) then DATEADD(day,1,c1.BucketDate)
else c1.BucketDate
end as NewBucketDate 
from CTE1 c1
where c1.BucketIndicator='T'

),
CTE3 AS(
select c2.BucketIndicator, c2.NewBucketDate as BucketDate  
from CTE2 c2
UNION
select c.BucketIndicator, c.BucketDate as BucketDate
from CTE c
),
CTE4 AS(

select c.BucketDate, ROW_NUMBER() OVER( ORDER BY c.BucketDate, c.BucketIndicator) as BucketNo
from CTE3 c),
CTE5 AS(
select  c1.BucketDate, c1.BucketNo, c1.BucketDate as BucketStartDate, LEAD(c1.BucketDate) OVER(ORDER BY c1.BucketNo) as BucketEndDate
from CTE4 c1

),
CTE6 AS(
select c5.BucketStartDate, c5.BucketEndDate from CTE5 c5
where c5.BucketEndDate is not null
and DATEDIFF(day, c5.BucketStartDate,c5.BucketEndDate) > 1
UNION
select c5.BucketStartDate, c5.BucketEndDate from CTE5 c5
where c5.BucketEndDate is not null
and DATEDIFF(day, c5.BucketStartDate,c5.BucketEndDate)=0)

Select * from CTE OPTION (MAXRECURSION 0);

fork query

edited aug 6

created aug 1 anonymous