Find missing date ranges between two dates

Question

I have table which having data of some date ranges. when user select start date and end date then the result set will be like all date ranges between that 2 dates also all missing date ranges between that 2 dates.
For Example:
DateRangesTable

ID| fromdate  | todate    |
----------------------------
1 | 5-May-21  | 10-May-21 |
2 | 17-May-21 | 25-May-21 |

this is my main table ,I mention below all result set which I wanted with above table

if user select : 5-May-2021 to 25-May-2021
Expected Result :

ID| fromdate  | todate    |
----------------------------
1 | 5-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |

if user select : 6-May-2021 to 23-May-2021
Expected Result :

ID| fromdate  | todate    |
-----------------------------
1 | 6-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 23-May-21 |

if user select : 1-May-2021 to 28-May-2021
Expected Result :

ID| fromdate  | todate    |
----------------------------
1 | 1-May-21  | 4-May-21  |
1 | 5-May-21  | 10-May-21 |
0 | 11-May-21 | 16-May-21 |
2 | 17-May-21 | 25-May-21 |
2 | 26-May-21 | 28-May-21 |

Here some question which is not to similar but try to find:

SQL Find missing date ranges

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

Thanks in advance.

I don't have permission to share code .but I did this kind logic : ``` declare @startdate datetime declare @enddate datetime set @startdate = '2021-04-24 00:00:00.000' set @enddate = '2021-06-30 00:00:00.000' insert into @tempPeriod select ID , FromDate , ToDate , CID = lead(ID,1,null) over (Order By fromdate) from tablename where (FromDate >= @startdate and FromDate <= @enddate) or (ToDate >= @startdate and ToDate <= @enddate) ``` — shubham kule, May 27 '21 at 09:03
[edit] any clarifications directly into the question. We don't want to see live code, we expect you to create a [mre] using test data and test code. — Dale K, May 27 '21 at 09:47
I don't understand the last expect result set; why does the first row have an `ID` of `1`, when those dates don't exist in the table, and why does the last have an `ID` of `2` for the same reason? — Thom A, May 27 '21 at 10:13
@DaleK it's my first question on this site and im trying this by my self. I understand what you are saying I will work on my mistake thanks for you guidance. — shubham kule, May 27 '21 at 10:48
@Larnu, Sorry you are right by mistake I add ID Wrong ID's on last result set I wanted 0 ID of that records which is create Virtually. thanks — shubham kule, May 27 '21 at 10:51
Yes, as I note my my [answer](https://stackoverflow.com/a/67720200/2029983) I *assumed* that to be the case, @shubhamkule . — Thom A, May 27 '21 at 11:04

Thom A · Accepted Answer · 2021-05-27T11:04:48.667

A note, I am assuming here that the expected results for your final expected results is wrong here as it doesn't match the other 2. The last and first rows in the expected results for the last set both have a value for ID that isn't 0, but no explanation of why they do is given. I therefore assume the value should be 0 like the row in the "middle".

To do this, I use a Tally to get all the dates between the date range you need; the Tally is limited to 1,000 rows, a little shy of 3 years, but you can cross join to N more if you need more rows. I then use that tally to create an inline calendar table. Next I LEFT JOIN that calendar to your data, and use a gaps and island method to put the values into groups. Finally I then aggregate on those groups, getting the MIN and MAX date in each one:

USE Sandbox;
GO

CREATE TABLE dbo.YourTable (ID int,
                            FromDate date,
                            ToDate date);
INSERT INTO dbo.YourTable
VALUES(1,'20210505','20210510'),
      (2,'20210517','20210525');
GO

DECLARE @StartDate date = '20210501',
        @EndDate date = '20210528';

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT 0 AS I
    UNION ALL
    SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2, N N3), --1000 days
Dates AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS [Date],
           T.I
    FROM Tally T),
Grps AS(
    SELECT D.[Date],
           YT.ID,
           D.I - ROW_NUMBER() OVER (PARTITION BY ID ORDER BY D.[Date]) AS Grp
    FROM Dates D
         LEFT JOIN dbo.YourTable YT ON D.[Date] >= YT.FromDate AND D.[Date] <= YT.ToDate)
SELECT ISNULL(MAX(G.ID),0) AS ID,
       MIN(G.[Date]) AS FromDate,
       MAX(G.[Date]) AS ToDate
FROM Grps G
GROUP BY G.Grp
ORDER BY FromDate ASC;

GO
DROP TABLE dbo.YourTable;

db<>fiddle

thanks for your quick response. it's work for me thank yo. – shubham kule May 27 '21 at 11:35 — shubham kule, May 27 '21 at 11:35

Gordon Linoff · Answer 2 · 2021-05-27T11:38:59.050

You can use union all for this:

-- first get the existing rows
select id,
       (case when fromdate < @fromdate then @fromdate else fromdate end) as fromdate,
       (case when todate > @todate then @todate else todate end) as to_date
from t
where fromdate < @todate and
      todate > @fromdate
union all
-- then the in-between rows
select 0, dateadd(day, 1, todate) as fromdate, next_fromdate as todate
from (select t.*,
             dateadd(day, -1, lead(fromdate) over (order by fromdate)) as next_fromdate
      from t
     ) t
where fromdate >= @fromdate and todate <= @todate and
      next_todate is not null
union all
-- then the earliest record, if any
select 0, @fromdate, min(fromdate)
from t
where todate > @fromdate
having @fromdate < min(fromdate) 
union all
-- then the final record, if any
select 0, max(todate), @todate
from t
where fromdate < @todate
having @todate > max(todate);

Here is a db<>fiddle.

Find missing date ranges between two dates

2 Answers2