0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for Tridion developers and administrators

DECLARE @HorizonStartDate date = '2020-01-01';
DECLARE @HorizonEndDate date = '2020-12-31';
DECLARE @ValidFrom date = '2020-01-02';
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 CTE2 OPTION (MAXRECURSION 0);

fork query

edited aug 4

created aug 1 anonymous