SQL Server - identify date range gaps

Question

I want to be able to identify date gaps in my network data. For example:

network 1: 1/1/12 (start) - 12/31/2014 (end)
network 2: 1/1/17 (start) - 12/31/9999 (end)

In the above example there is a gap between network 1 end date and network 2 start date in the years 2015 and 2016.

Because this is SQL, what is the table schema you are trying to form your query on. Why is network 2 having 12/31/9999 as date. You could have simply left it undefined(i.e undefined upper limit) — Acewin, Aug 28 '17 at 21:27
Does this Stack Overflow question resolve your issue? https://stackoverflow.com/questions/9604400/sql-query-to-show-gaps-between-multiple-date-ranges — bbrumm, Aug 28 '17 at 22:13
the 9999 is just an example it can be 2018 or any other future year. My expected result is the person associated to these networks. So Person A has the 'OPAR' network with sequence 1 of 1/1/12 (start) - 12/31/2014 (end) and sequence 2 of: 1/1/17 (start) - 12/31/xxxx (end) — user3806259, Aug 28 '17 at 22:15
You should provide more data as well as a sample of expected output in your OP. Your current sample data doesn't even mention "Person A". As of right now, the most anyone can do is tell you that functions like `LAG()` or `LEAD()` and `DATEDIFF` may help you. — ZLK, Aug 28 '17 at 22:30

Lamar · Answer 1 · 2017-08-28T23:24:51.913

You need to have discrete values to be able to compare dates and find gaps. Since datetime is not quite discrete in a sense of days (you have hours, minutes and seconds), you might think of creating a calendar table, something like:

|date      |
|----------|
|01-01-2000|
|01-02-2000|
|01-03-2000|
...
12-31-2050

I doubt that you will need to fill it till year 9999.

The calendar table can contain either dates, months, or years, depending on your requirement.

After you have such table, you can left join the calendar table with your data table to find gaps, depending on your criteria. Something like:

SELECT date, table.id 
FROM calendar
LEFT JOIN table on calendar.date  BETWEEN table.start_date AND table.end_date
WHERE table.id IS NULL

This is a basic hypothetical example, since you haven't provided the full information about your tables and query, but you can work on it to obtain the result you want.

SQL Server - identify date range gaps

1 Answers1