Pfthb

I have a list of Date Ranges in a table that can be open ended (enddate = null):

Index startdate enddate
1 2018-07-13 NULL
2 2018-11-14 2018-11-16
3 2018-11-15 2018-11-15

Query for Test Data:

DECLARE @ScheduleTable Table([Index] int not null, StartDate DateTime not null, EndDate DateTime null)
insert into @ScheduleTable ([Index], StartDate, EndDate)
values
(1,'2018-07-13',null)
, (2,'2018-11-14','2018-11-16')
, (3,'2018-11-15','2018-11-15')
select*from @ScheduleTable

How can I write a query that will "fill in the holes" and return the following results:

Index startdate enddate
1 2018-07-13 2018-11-13
2 2018-11-14 2018-11-14
3 2018-11-15 2018-11-15
2 2018-11-16 2018-11-16
1 2018-11-17 NULL

Query displaying expected results:

select 
1 as [Index], '2018-07-13' as StartDate, '2018-11-13' as EndDate
UNION ALL
select 
2 as [Index], '2018-11-14', '2018-11-14'
UNION ALL
select 
3 as [Index], '2018-11-15', '2018-11-15'
UNION ALL
select 
2 as [Index], '2018-11-16', '2018-11-16'
UNION ALL
select 
1 as [Index], '2018-11-17', null

I'd prefer an answer that doesn't involve parameters/ temp table etc. I have a Date Dimension table if that would help.

In the above example, the entry with Index=1 is open-ended and starts on 7.13. It is interrupted by Index=2 on 11.14. Index=2 is then interrupted by Index=3 on 11.15. Index=2 then starts again on 11.16. Followed by Index=1 starting up again on 11.17

The Index determines the order of preference, so Index=2 will override Index=1 on 11.14 - 11.16 and Index=3 will override Index=2 on 11.15.



Here is my current query using lead():

DECLARE @MinDate DateTime = '2015-01-01'
DECLARE @MaxDate DateTime = '2020-01-01'

select
row_number() over(partition by dealid order by ss.StartDate, ss.id) as [Index]
, ss.startdate
, ss.enddate
, case when ss.enddate is null then
 dateadd(d,-1,lead(ss.startdate,1,@MaxDate) over(partition by dealid order by ss.startdate, ss.id)) 
 else ss.enddate end
 as EndDate
from
[dbo].[Schedule]ss
where ss.enabled = 1

I was able to solve the problem using the following:

Steps:

• Obtain table of Dates from DateDimension table


• Join schedules to DateDimension table


• row_number the schedules to determine which one takes precedence for a given date


• rank results, order by date


• dense_rank results, partition by schedule Id, order by date


• subtract the dense_rank results from the rank results to create a unique Id for each group of contiguous dates


• Obtain the min and max date for each range of dates

Query to populate test data:

DECLARE @ScheduleTable Table([Index] int not null, StartDate DateTime not null, EndDate DateTime null)
insert into @ScheduleTable ([Index], StartDate, EndDate)
values
(1,'2018-07-13',null)
, (2,'2018-11-14','2018-11-16')
, (3,'2018-11-15','2018-11-15')

Solution:

DECLARE @MinDate Date = dateadd(year,-2,getdate())
DECLARE @MaxDate DateTime = dateadd(year,2,getdate())

select 
min(dt) as StartDate
, max(dt) as EndDate
, dense_rank() over(Order by [Index]) [Index]
from
(
select
--Create "groups" using a raw Rank minus dense_rank, partitioned by [Index]
rank() over(order by dt) - dense_rank() over(partition by [Index] order by dt) qlt,
[Index], dt
from
(
select
--Apply row_number to identify which schedule takes precedence on a given day
--Index=2 takes precedence over Index=1
row_number() over(partition by inr.[date] order by ss.[Index] desc) rm,
[date] dt
, ss.*
from
(
 --Obtain Table of Dates from DateDimension table
 select
 [date]
 from
 [dbo].[DateDimension]dd
 where dd.[date] >= @MinDate
 and dd.[date] <= @MaxDate
)inr
 --join schedules to DateDimension table
 left join 
 (
 select *
 from
 @ScheduleTable
 )ss
 on ss.StartDate <= inr.[date]
 and (ss.enddate >= inr.[date]
 or ss.enddate is null)

)inr2
--Exclude any Schedule that is not row_number=1
where inr2.rm = 1
and inr2.[Index] is not null
)inr3
--Group on Index first then Rank minus dense_rank, partitioned by [Index]
group by [Index], qlt
order by StartDate
, [Index]

Results:

StartDate EndDate Index
2018-07-13 2018-11-13 1
2018-11-14 2018-11-14 2
2018-11-15 2018-11-15 3
2018-11-16 2018-11-16 2
2018-11-17 2020-11-12 1