CODEWITHSUNDEEP
sqldatabase
Lynchie
Lynchie

Reputation: 1149

SQL Finding Missing Dates Between Ranges

I have a table of jobs that have ran for different source systems. These have a "RunDate" and then the FromDate and ToDate.

I want to find the gaps where we are missing any dates in the FromDate and ToDate fields to make sure that we have covered the data in those periods.

Many examples I've looked at work where a single date misses in a single column of ranges, however I have a From and To range that I need to test and ultimately work out where a date may be missed.

CREATE TABLE #temptable ( [SourceSystem] nchar(3), [RunDate] datetime, [ResubmitCount] int, [FromDate] date, [ToDate] date )
INSERT INTO #temptable
VALUES
( N'ILG', N'2021-07-28T15:35:23.207', 0, N'2021-06-01T00:00:00', N'2021-06-01T00:00:00' ), 
( N'ILG', N'2021-07-28T15:35:23.707', 0, N'2021-06-05T00:00:00', N'2021-06-06T00:00:00' ), 
( N'AAP', N'2021-07-28T15:35:23.833', 0, N'2021-06-01T00:00:00', N'2021-06-02T00:00:00' ), 
( N'AAP', N'2021-07-28T15:35:23.833', 0, N'2021-06-04T00:00:00', N'2021-06-04T00:00:00' ), 
( N'ZZP', N'2021-07-28T15:35:23.897', 0, N'2021-06-05T00:00:00', N'2021-06-05T00:00:00' )

DROP TABLE #temptable

So obviously using the example above I should be able to ascertain that the period between 2021-06-02 and 2021-06-04 for SourceSystem ILG and period 2021-06-03 to 2021-06-03 is missing for SourceSystem AAP.

Struggling to make it work for ranges, I can work with single dates but the system doesn't log them in this fasion.

UPDATE

I took the accepted answer and then tagged some code to it to be able to explode all the individual dates between the ranges specified.

Included the code in case anyone needs in the future.

WITH
  a AS (
    SELECT
      SourceSystem, FromDate, ToDate,
      LEAD(FromDate) OVER(
        PARTITION BY SourceSystem
        ORDER BY RunDate
      ) AS NextDate
    FROM dbo.WDSubmission ws
  ),
  gap_periods AS
  (
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
  --AND a.SourceSystem = 'OGI'  
  )  , E00(N) AS (SELECT 1 UNION ALL SELECT 1)
    ,E02(N) AS (SELECT 1 FROM E00 a, E00 b)
    ,E04(N) AS (SELECT 1 FROM E02 a, E02 b)
    ,E08(N) AS (SELECT 1 FROM E04 a, E04 b)
    ,E16(N) AS (SELECT 1 FROM E08 a, E08 b)
    ,E32(N) AS (SELECT 1 FROM E16 a, E16 b)
    ,cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E32)
    ,DateRange AS
(
    SELECT ExplodedDate = DATEADD(DAY,N - 1,'2021-01-01')
    FROM cteTally
    WHERE N <= DATEDIFF(DAY,'2021-01-01',GETDATE())
)
SELECT *
FROM gap_periods eh
JOIN DateRange d ON d.ExplodedDate >= eh.GapBeg
 AND d.ExplodedDate <= eh.GapFin;

Upvotes: 1

Views: 1660

Answers (2)

Andrei Odegov
Andrei Odegov

Reputation: 3429

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

Upvotes: 1

Ravikiran Rao
Ravikiran Rao

Reputation: 34

Its hard to display the missing dates and also it would be too much information to analyze. Bbut you can get the number of days skipped in between using the below query :

select *,datediff(day,lag(ToDate)over(partition by sourcesystem order by ToDate),fromdate) from #temptable

The data would look something like this (last column tells the number of days skipped) :

enter image description here

Upvotes: 0

Related Questions