CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
Kratz
Kratz

Reputation: 4330

Filling in missing ranges in a set

This is a question on filing missing ranges of data. Specifically, I have a result set where each row contains a StartDate and EndDate value. Let's say I have:

Start     End
1/15      1/20
1/12      3/15

I would need a query that produces are adds the following row to the data:

1/21      2/11

Most other related questions are about filling gaps from a know set (like a list of dates). I this case I'm just looking for the start/end of the missing data.

Upvotes: 0

Views: 133

Answers (4)

user2023861
user2023861

Reputation: 8208

Assuming that you meant to write 1/21 instead of 2/21, here's a way to do it:

with dates as
(
    select '2016-01-15' as dtStart, '2016-01-20' as dtEnd union all
    select '2016-02-12', '2016-03-15' union all
    select '2016-03-21', '2016-04-11'
),

calcs as
(
    select 
        dateadd(day, 1, dtEnd) as rangeStart,
        (select dateadd(day, -1, min(dtStart)) 
            from dates d2 where d2.dtStart > d.dtEnd) as rangeEnd
    from dates d
)

select *
from calcs c
where c.rangeEnd >= c.rangeStart

The table dates is just three rows of sample dates. In thecalcs table, the rangeStart column is the next day after each dtEnd. The rangeEnd column takes the next dtStart and subtracts a day. Finally, the last row will have a null because there is now missing range after the last dtEnd, so I ignore the row with the null rangeEnd value.

Edit: If you're unfamiliar with the with statement in my code, it's a CTE. I used it here as a quick way to create a table with some sample data in it (dates) and a place to store calculations (calcs).

Edit2: Since you mentioned using a join in your comment, here's a way to do that:

with dates as
(
    select '2016-01-15' as dtStart, '2016-01-20' as dtEnd union all
    select '2016-02-12', '2016-03-15' union all
    select '2016-03-21', '2016-04-11'
),

calcs as
(
    select
        dateadd(day, 1, d1.dtEnd) as rangeStart,
        dateadd(day, -1, min(d2.dtStart)) as rangeEnd
    from dates d1
    join dates d2 on d1.dtEnd < d2.dtStart
    group by d1.dtEnd
)

select *
from calcs
where datediff(day, rangeStart, rangeEnd) >= 0

Edit3: updated the datediff inequality to include ranges of one day

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81950

Declare @Table table (Start Date,[End] Date)
Insert into @Table values
('2016-01-15','2016-01-20'),
('2016-02-12','2016-03-15')

;with cteBase as (
    Select *,Gap=DateDiff(DD,Lag([End],1,Start) over (Order By Start),Start) From @Table
)
Select Start=DateAdd(DD,1+Gap*-1,Start),[End]=DateAdd(DD,-1,Start) from cteBase Where Gap<>0

Returns

Start       End
2016-01-21  2016-02-11

Optional for full dataset

...
Union All
Select Start,[End] from cteBase
Order by Start

Upvotes: 0

Cato
Cato

Reputation: 3701

    select * from 
    (select DISTINCT t1.[end] + 1 'start', t2.start - 1 'end'
            from yourtable t1 
            CROSS JOIN 
            yourtable t2
                WHERE 
                    EXISTS(SELECT 0 FROM yourtable t3 WHERE t3.[end] < t2.[start]) 
                AND 
                    NOT EXISTS(SELECT 0 FROM yourtable t4 WHERE t4.start = t1.[end] + 1)
                AND 
                    NOT EXISTS(SELECT 0 FROM yourtable t5 WHERE T5.Start BETWEEN t1.[end] + 1 AND  t2.start - 1 OR T5.[end] BETWEEN t1.[end] + 1 AND t2.start - 1)
                AND
                    t1.[end] + 1 <= t2.start - 1
    UNION
        SELECT start,[end] from yourtable) dq
order by start

Upvotes: 0

Chris Albert
Chris Albert

Reputation: 2507

I would recommend creating a date table. This will keep your query simple and you can reuse in other queries. Below is a link to an article on creating a date table. Hope this helps.

https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Upvotes: 0

Related Questions