SQL how to write a query that return missing date ranges?

Question

I am trying to figure out how to write a query that looks at certain records and finds missing date ranges between today and 9999-12-31. My data looks like below:

ID      |start_dt                   |end_dt                     |prc_or_disc_1
10412   |2018-07-17 00:00:00.000    |2018-07-20 00:00:00.000    |1050.000000
10413   |2018-07-23 00:00:00.000    |2018-07-26 00:00:00.000    |1040.000000

So for this data I would want my query to return:

2018-07-10 | 2018-07-16
2018-07-21 | 2018-07-22
2018-07-27 | 9999-12-31

I'm not really sure where to start. Is this possible?

Answer 1

select
    CASE WHEN DATEDIFF(day, end_dt, ISNULL(LEAD(start_dt) over (order by ID), '99991231')) > 1 then end_dt +1 END as F1,
    CASE WHEN DATEDIFF(day, end_dt, ISNULL(LEAD(start_dt) over (order by ID), '99991231')) > 1 then ISNULL(LEAD(start_dt) over (order by ID) - 1, '99991231') END as F2
from t

Working SQLFiddle example is -> Here

FOR 2008 VERSION

SELECT 
    X.end_dt + 1 as F1,
    ISNULL(Y.start_dt-1, '99991231') as F2
FROM t X
LEFT JOIN (
SELECT 
      *
    , (SELECT MAX(ID) FROM t WHERE ID < A.ID) as ID2
FROM t A) Y ON X.ID = Y.ID2
WHERE DATEDIFF(day, X.end_dt, ISNULL(Y.start_dt, '99991231')) > 1

Working SQLFiddle example is -> Here

Answer 2

You can do that using the lag() function in MS SQL (but that is available starting with 2012?).

 with myData as
    (
      select *, 
      lag(end_dt,1) over (order by start_dt) as lagEnd
      from myTable),
    myMax as
    (
      select Max(end_dt) as maxDate from myTable
    )
    select dateadd(d,1,lagEnd) as StartDate, dateadd(d, -1, start_dt) as EndDate
    from myData
    where lagEnd is not null and dateadd(d,1,lagEnd) < start_dt
    union all
    select dateAdd(d,1,maxDate) as StartDate, cast('99991231' as Datetime) as EndDate 
    from myMax
    where maxDate < '99991231';

If lag() is not available in MS SQL 2008, then you can mimic it with row_number() and joining.

Answer 3

http://sqlfiddle.com/#!18/65238/1

SELECT
  *
FROM
(
  SELECT
    end_dt+1                            AS start_dt,
    LEAD(start_dt-1, 1, '9999-12-31')
      OVER (ORDER BY start_dt)
                                        AS end_dt
  FROM
    yourTable
)
  gaps
WHERE
  gaps.end_dt >= gaps.start_dt

I would, however, strongly urge you to use end dates that are "exclusive". That is, the range is everything up to but excluding the end_dt.

That way, a range of one day becomes '2018-07-09', '2018-07-10'.

It's really clear that my range is one day long, if you subtract one from the other you get a day.

Also, if you ever change to needing hour granularity or minute granularity you don't need to change your data. It just works. Always. Reliably. Intuitively.

If you search the web you'll find plenty of documentation on why inclusive-start and exclusive-end is a very good idea from a software perspective. (Then, in the query above, you can remove the wonky +1 and -1.)

Answer 4

This should work in 2008, it assumes that ranges in your table do not overlap. It will also eliminate rows where the end_date of the current row is a day before the start date of the next row.

  with dtRanges as (
       select start_dt, end_dt, row_number() over (order by start_dt) as rownum 
       from table1
  )

  select t2.end_dt + 1, coalesce(start_dt_next -1,'99991231')
  FROM 
    (  select dr1.start_dt, dr1.end_dt,dr2.start_dt as start_dt_next
       from dtRanges dr1
       left join dtRanges dr2 on dr2.rownum = dr1.rownum + 1
    ) t2
  where 
  t2.end_dt + 1 <>  coalesce(start_dt_next,'99991231')

Answer 5

You may want to take a look at this: http://sqlfiddle.com/#!18/3a224/1 You just have to edit the begin range to today and the end range to 9999-12-31.

Answer 6

This solves your case, but provide some sample data if there will ever be overlaps, fringe cases, etc.

Take one day after your end date and 1 day before the next line's start date.

DECLARE @ TABLE (ID int, start_dt DATETIME, end_dt DATETIME, prc VARCHAR(100))

INSERT INTO @ (id, start_dt, end_dt, prc)
VALUES 
(10410,   '2018-07-09 00:00:00.00','2018-07-12 00:00:00.000','1025.000000'),
(10412,   '2018-07-17 00:00:00.00','2018-07-20 00:00:00.000','1050.000000'),
(10413,   '2018-07-23 00:00:00.00','2018-07-26 00:00:00.000','1040.000000')


SELECT DATEADD(DAY, 1, end_dt)
, DATEADD(DAY, -1, LEAD(start_dt, 1, '9999-12-31') OVER(ORDER BY id) )
FROM @