Filling gaps with date ranges in SQL

Question

I have an events table that has a start and end date columns (events do not overlap), sample data

if object_id('tempdb..#SourceTable') is not null
begin
    drop table #SourceTable
end

create table #SourceTable
(
    Id int identity(1,1) not null,
    WindowRange varchar(15) not null,
    StartDatetime datetime null,
    EndDatetime datetime null
)


insert into #SourceTable
(
    WindowRange,
    StartDatetime,
    EndDatetime
)
values 
    ('04:20 - 05:36', '2015-08-31 04:20:01.890', '2015-08-31 05:36:14.290' ),
    ('00:20 - 01:24', '2015-08-31 00:20:01.487', '2015-08-31 01:24:52.983' ),
    ('20:20 - 21:27', '2015-08-30 20:20:01.177', '2015-08-30 21:27:53.317' ),
    ('16:20 - 17:28', '2015-08-30 16:20:01.133', '2015-08-30 17:28:24.173' ),
    ('12:20 - 13:30', '2015-08-30 12:20:01.273', '2015-08-30 13:30:38.370' )

Sample Output

Id  WindowRange     StartDatetime           EndDatetime
1   04:20 - 05:36   2015-08-31 04:20:01.890 2015-08-31 05:36:14.290
2   00:20 - 01:24   2015-08-31 00:20:01.487 2015-08-31 01:24:52.983
3   20:20 - 21:27   2015-08-30 20:20:01.177 2015-08-30 21:27:53.317
4   16:20 - 17:28   2015-08-30 16:20:01.133 2015-08-30 17:28:24.173
5   12:20 - 13:30   2015-08-30 12:20:01.273 2015-08-30 13:30:38.370

I would like to have additional rows that fill the gaps in the ranges, for the example above

Expected Output

Id  WindowRange     StartDatetime           EndDatetime
1   04:20 - 05:36   2015-08-31 04:20:01.890 2015-08-31 05:36:14.290
2   01:24 - 04:20   2015-08-31 01:24:52.983 2015-08-31 04:20:01.890
3   00:20 - 01:24   2015-08-31 00:20:01.487 2015-08-31 01:24:52.983
4   00:00 - 00:20   2015-08-31 00:00:00.000 2015-08-31 00:20:01.487
5   21:27 - 23:59   2015-08-30 21:27:53.317 2015-08-30 23:59:59.999
6   20:20 - 21:27   2015-08-30 20:20:01.177 2015-08-30 21:27:53.317
7   17:28 - 20:20   2015-08-30 17:28:24.173 2015-08-30 20:20:01.177
8   16:20 - 17:28   2015-08-30 16:20:01.133 2015-08-30 17:28:24.173
9   13:30 - 16:20   2015-08-30 13:30:38.370 2015-08-30 16:20:01.133
10  12:20 - 13:30   2015-08-30 12:20:01.273 2015-08-30 13:30:38.370

I have tried using a common table expression with a window function but can't seem to get it right

;with myCTE as
(
    select 
        row_number() over (order by EndDatetime desc) as SeqNo, 
        StartDatetime,
        EndDatetime
    from #SourceTable
)


select 
    t1.SeqNo as [T1SeqNo],
    t2.SeqNo as [T2SeqNo],
    t1.StartDatetime as [T1Start],
    t1.EndDatetime as [T1End],
    t2.StartDatetime as [T2Start],
    t2.EndDatetime as [T2End]
from myCTE t1
left join myCTE t2
    on t1.SeqNo = t2.SeqNo - 1

Any suggestion/help would be greatly appreciated.

possible duplicate of [SQL Query to show gaps between multiple date ranges](http://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges) — Bulat, Sep 01 '15 at 10:11

score 2 · Answer 1 · answered Sep 01 '15 at 10:20

2

    ;with myCTE as
(
    select 
        row_number() over (order by EndDatetime desc) as SeqNo, 
        StartDatetime,
        EndDatetime
    from #SourceTable
)

select ROW_NUMBER() over (order by T1Start DESC), *
from (
select 
    t1.StartDatetime as [T1Start],
    t1.EndDatetime as [T1End]
from  myCTE t1
UNION ALL
select 
    t1.EndDatetime as [T1Start],
    t2.StartDatetime as [T1SEnd]
from  myCTE t1
inner join myCTE t2
    on t1.SeqNo = t2.SeqNo + 1

) as t
order by T1Start DESC

answered Sep 01 '15 at 10:20

Polux2

552
3
12

Nice one! Although your solution does not give the exact expected output as the windows should be partitioned per day, thus there should be no overlapping ranges for the gaps i.e. `2015-08-30 21:27:53.317 2015-08-31 00:20:01.487` should be `2015-08-30 21:27:53.317 2015-08-30 23:59:59.999` etc. Nonetheless, I appreciate the help! +1 for the effort and the idea. – chridam Sep 01 '15 at 10:27
From all the suggestions, this is the simplest and most effective one. I was able to adjust it to my needs and works wonders. – FAB Aug 14 '19 at 13:07

user2955677 · Accepted Answer · 2015-09-01T11:01:30.403

DECLARE @WindowRange varchar(15), 
        @StartDatetime datetime, 
        @EndDatetime datetime, 
        @StartDate date, 
        @EndDate date, 
        @NextStartDatetime datetime, 
        @NextEndDatetime datetime

DECLARE yourCursor CURSOR  FORWARD_ONLY READ_ONLY FOR
    SELECT  StartDatetime, EndDatetime FROM #SourceTable order by StartDatetime
OPEN yourCursor

FETCH NEXT FROM yourCursor INTO @StartDatetime, @EndDatetime
IF @@FETCH_STATUS = 0 
    BEGIN
        FETCH NEXT FROM yourCursor INTO @NextStartDatetime, @NextEndDatetime
        WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @StartDate = @StartDatetime
                SET @EndDate = @EndDatetime
                IF @EndDate > @StartDate
                    BEGIN
                        SET @WindowRange = LEFT(CONVERT(varchar, @StartDatetime, 108), 5)  + ' - ' + LEFT(CONVERT(varchar, @EndDate, 108), 5)
                        INSERT INTO #SourceTable (WindowRange, StartDatetime, EndDatetime) VALUES (@WindowRange, @StartDatetime, @EndDate)


                        SET @WindowRange = LEFT(CONVERT(varchar, @StartDatetime, 108), 5)  + ' - ' + LEFT(CONVERT(varchar, @EndDate, 108), 5)
                        INSERT INTO #SourceTable (WindowRange, StartDatetime, EndDatetime) VALUES (@WindowRange, @EndDate, @EndDatetime)
                    END

                    SET @StartDate = @EndDatetime
                    SET @EndDate = @NextStartDatetime

                    IF @EndDate > @StartDate
                    BEGIN
                        SET @WindowRange = LEFT(CONVERT(varchar, @EndDatetime, 108), 5)  + ' - ' + '00:00' --@StartDate
                        INSERT INTO #SourceTable (WindowRange, StartDatetime, EndDatetime) VALUES (@WindowRange, @EndDatetime, @StartDate)


                        SET @WindowRange = '00:00'  + ' - ' + LEFT(CONVERT(varchar, @NextStartDatetime, 108), 5) --@EndDate
                        INSERT INTO #SourceTable (WindowRange, StartDatetime, EndDatetime) VALUES (@WindowRange, @EndDate, @NextStartDatetime)
                    END



                SET @WindowRange = LEFT(CONVERT(varchar, @EndDatetime, 108), 5)  + ' - ' + LEFT(CONVERT(varchar, @NextStartDatetime, 108), 5)
                INSERT INTO #SourceTable (WindowRange, StartDatetime, EndDatetime) VALUES (@WindowRange, @EndDatetime, @NextStartDatetime)
                SET @StartDatetime = @NextStartDatetime
                SET @EndDatetime = @NextEndDatetime
                FETCH NEXT FROM yourCursor INTO @NextStartDatetime, @NextEndDatetime
            END
        END
CLOSE yourCursor;
DEALLOCATE yourCursor;

select * from  #SourceTable order by StartDatetime

Cheers for this different approach using a cursor, really helpful as it gives me an idea on how to solve the problem though it doesn't exactly address the issue of restricting the intervals/gaps within a day i.e. `2015-08-30 21:27:53.317 2015-08-31 00:20:01.487` should be `2015-08-30 21:27:53.317 2015-08-30 23:59:59.999` etc. Nonetheless +1 for the help and effort! — chridam, Sep 01 '15 at 10:36
Please note the use of the date datatype for removing the time part and thus setting it to 00:00. — user2955677, Sep 01 '15 at 11:03

score 1 · Answer 3 · answered Sep 01 '15 at 11:02

I have made an SQL Server cursor code that nearly close to your expected result. The different here is on the 5th record that you expect "2015-08-30 23:59:59.999" but in SQL Server "2015-08-30 23:59:59.999" will be read as "2015-09-01 00:00:00.000". Hope this help

DECLARE @temp TABLE(
    Id int identity(1,1) not null,
    WindowRange varchar(15) not null,
    StartDatetime datetime null,
    EndDatetime datetime null
)

INSERT INTO @temp
SELECT TOP 1 WindowRange, StartDatetime, EndDatetime 
FROM #SourceTable ORDER BY StartDateTime

DECLARE @curStartDateTime DATETIME
DECLARE @curEndDateTime DATETIME

DECLARE @prevStartDateTime DATETIME
DECLARE @prevEndDateTime DATETIME

DECLARE @iteration INT
SET @iteration = 0

DECLARE @timeRange CURSOR

SET @timeRange = CURSOR FOR
SELECT StartDatetime, EndDatetime FROM #SourceTable ORDER BY StartDateTime

OPEN @timeRange

FETCH NEXT FROM @timeRange INTO @curStartDateTime, @curEndDateTime

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @iteration <> 0
    BEGIN
        IF CONVERT(VARCHAR(8), @curStartDateTime, 112) = CONVERT(VARCHAR(8), @prevEndDateTime, 112)
        BEGIN
            IF CONVERT(VARCHAR(23), @curStartDateTime, 121) <> CONVERT(VARCHAR(23), @prevEndDateTime, 121)
            BEGIN
                INSERT INTO @temp
                SELECT 
                    CONVERT(VARCHAR(5), @prevEndDateTime, 108) + ' - ' + CONVERT(VARCHAR(5), @curStartDateTime, 108)
                    , @prevEndDateTime
                    , @curStartDateTime
            END
        END
        ELSE
        BEGIN
            INSERT INTO @temp
                SELECT 
                    CONVERT(VARCHAR(5), @prevEndDateTime, 108) + ' - 23:59'
                    , @prevEndDateTime
                    , CONVERT(VARCHAR(8), @curStartDateTime, 112) + ' 23:59:59.999'

            INSERT INTO @temp
                SELECT 
                    '00:00 - ' + CONVERT(VARCHAR(5), @curStartDateTime, 108)
                    , CONVERT(VARCHAR(8), DATEADD(day, 1, @prevEndDateTime), 112) + ' 00:00:00.000'
                    , @curStartDateTime
        END

        INSERT INTO @temp
        SELECT 
            CONVERT(VARCHAR(5), @curStartDateTime, 108) + ' - ' + CONVERT(VARCHAR(5), @curEndDateTime, 108)
            , @curStartDateTime
            , @curEndDateTime
    END

    SET @prevStartDateTime = @curStartDateTime
    SET @prevEndDateTime = @curEndDateTime

    SET @iteration = @iteration + 1
    FETCH NEXT FROM @timeRange INTO @curStartDateTime, @curEndDateTime
END

CLOSE @timeRange
DEALLOCATE @timeRange

SELECT * FROM @temp ORDER BY StartDatetime DESC

Cheers @FathirMohamad, your answer was quite helpful :-) – chridam Sep 01 '15 at 11:16 — chridam, Sep 01 '15 at 11:16

Filling gaps with date ranges in SQL

3 Answers3