DECLARE @HorizonStartDate date = '2020-01-01'; DECLARE @H...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Literature Meta

Q&A about the site for scholars and enthusiasts of literature

DECLARE @HorizonStartDate date = '2020-01-01';
DECLARE @HorizonEndDate date = '2020-12-31';
DECLARE @ValidFrom date = '2020-12-01';
DECLARE @ValidEnd date = '2020-01-30';
DECLARE @ValidFrom1 date = '2020-02-01';
DECLARE @ValidEnd1 date = '2020-04-30';

WITH CTE AS(
SELECT @HorizonStartDate as BucketDate, 'F' as BucketIndicator
UNION
SELECT @HorizonEndDate as BucketDate,'T' as BucketIndicator
UNION
SELECT @ValidFrom as BucketDate, 'F' as BucketIndicator where 
@ValidFrom BETWEEN @HorizonStartDate AND @HorizonEndDate
UNION
SELECT @ValidEnd as BucketDate, 'T' as BucketIndicator where 
@ValidEnd BETWEEN @HorizonStartDate AND @HorizonEndDate
UNION
SELECT @ValidFrom1 as BucketDate, 'F' as BucketIndicator where 
@ValidFrom1 BETWEEN @HorizonStartDate AND @HorizonEndDate
UNION
SELECT @ValidEnd1 as BucketDate, 'T' as BucketIndicator where 
@ValidEnd1 BETWEEN @HorizonStartDate AND @HorizonEndDate
),
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 CTE6 OPTION (MAXRECURSION 0);

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results