SQL Find Missing Date Ranges

SQL Finding Missing Dates Between Ranges

Try this:

WITH
  a AS (
    SELECT
      SourceSystem, FromDate, ToDate,
      LEAD(FromDate) OVER(
        PARTITION BY SourceSystem
        ORDER BY RunDate
      ) AS NextDate
    FROM #temptable
  )
SELECT
  SourceSystem,
  DATEADD(DAY, 1, ToDate) AS GapBeg,
  DATEADD(DAY, -1, NextDate) AS GapFin
FROM a
WHERE
  NextDate IS NOT NULL AND
  DATEADD(DAY, -2, NextDate) >= ToDate;

Result:

+--------------+------------+------------+
| SourceSystem |   GapBeg   |   GapFin   |
+--------------+------------+------------+
| AAP          | 2021-06-03 | 2021-06-03 |
| ILG          | 2021-06-02 | 2021-06-04 |
+--------------+------------+------------+

db-fiddle

find missing dates from date range

I guess you could always generate the date sequence and just use a NOT IN to eliminate the dates that actually exist. This will max out at a 1024 day range, but is easy to shrink or extend, the date column is called "mydate" and is in the table "table1";

SELECT * FROM (
  SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) day 
  FROM 
   (SELECT 0 t4    UNION ALL SELECT 1   UNION ALL SELECT 2   UNION ALL SELECT 3  ) t4,
   (SELECT 0 t16   UNION ALL SELECT 4   UNION ALL SELECT 8   UNION ALL SELECT 12 ) t16,   
   (SELECT 0 t64   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48 ) t64,      
   (SELECT 0 t256  UNION ALL SELECT 64  UNION ALL SELECT 128 UNION ALL SELECT 192) t256,     
   (SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024     
  ) b 
WHERE day NOT IN (SELECT mydate FROM Table1) AND day<'2013-08-13';

From the "I would add an SQLfiddle if it wasn't down" dept.

Thanks for help here is the query i am end up with and its working

SELECT * FROM
(
    SELECT DATE_ADD('2013-08-01', INTERVAL t4+t16+t64+t256+t1024 DAY) missingDates 
        FROM 
    (SELECT 0 t4    UNION ALL SELECT 1   UNION ALL SELECT 2   UNION ALL SELECT 3  ) t4,
    (SELECT 0 t16   UNION ALL SELECT 4   UNION ALL SELECT 8   UNION ALL SELECT 12 ) t16,   
    (SELECT 0 t64   UNION ALL SELECT 16  UNION ALL SELECT 32  UNION ALL SELECT 48 ) t64,      
    (SELECT 0 t256  UNION ALL SELECT 64  UNION ALL SELECT 128 UNION ALL SELECT 192) t256,     
    (SELECT 0 t1024 UNION ALL SELECT 256 UNION ALL SELECT 512 UNION ALL SELECT 768) t1024     
) b 
WHERE
    missingDates NOT IN (SELECT DATE_FORMAT(start_date,'%Y-%m-%d')
            FROM
                working GROUP BY start_date)
    AND
    missingDates < '2013-08-13';

SQL Find missing date ranges

You can use CTE and write a query as:

declare @StartDate DATE, @EndDate DATE
set @StartDate = '2013-09-01';
set @EndDate = '2013-09-30';

  WITH DateRange(Date) AS
     (
         SELECT
             @StartDate Date
         UNION ALL
         SELECT
             DATEADD(day, 1, Date) Date
         FROM
             DateRange
         WHERE
             Date < @EndDate
     )

     SELECT 'webshop',Date 
     FROM DateRange
     EXCEPT 
     SELECT DataSet,DateRange
     FROM ImportedDateRange
     WHERE DataSet='webshop'
     --You could remove Maximum Recursion level constraint by specifying a MaxRecusion of zero
     OPTION (MaxRecursion 10000);

Find missing date ranges between two dates

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

Display data for all date ranges including missing dates

Did you check, if datefrom and dateto is in correct range?

Minimum value of DateTime field is 1753-01-01 and maximum value is 9999-12-31.

Look at your source table to check initial values.

---

Related Topics