Search code examples
sqlsql-servermissing-data

Find Missing Data between interval

I have a system that inserts register in same minutes every hour, for example:

DateTime              Value
2023/05/01 06:14        10
2023/05/01 06:29        15
2023/05/01 06:44        21
2023/05/01 06:59        12

So i want make a query that returns me possible missing records in a given range.

DateTime              Value
2023/05/01 06:14        10
2023/05/01 06:29        15
2023/05/01 06:44        21
2023/05/01 06:59        12
2023/05/01 07:29        10
2023/05/01 07:44        17
2023/05/01 08:14        20

In this record i want a query returns me:

DateTime
2023/05/01 07:14
2023/05/01 07:59

I've read some threads with similar problems like this one: Find Missing Dates in Data

But i can't find the solution.

Any ideas? Thanks a lot!

Solution

  • You must set interval based on the minutes(I set 15 minute )

    I got the start date and end date based on your data, you can set it as well

    DECLARE @StartDate DATETIME 
DECLARE @EndDate DATETIME
DECLARE @interval int=15

SET @StartDate =(select min(_DateTime) from #test)
SET @EndDate = (select max(_DateTime) from #test)

;WITH Dates(Date) AS
(
    SELECT DATEADD(MINUTE, @interval, @StartDate) AS Date
    UNION ALL
    SELECT DATEADD(MINUTE, @interval, Date) AS Date
    FROM Dates
    WHERE Date < @EndDate
)
SELECT a.Date
FROM  Dates a
left join #test b on a.Date=b._DateTime
where b._DateTime is null
option (maxrecursion 0)

    result: |Date| |--| |2023-05-01 07:14:00.000| |2023-05-01 07:59:00.000|

    Base Data:

    create table #test(_DateTime DateTime,_value int)
insert into #test(_DateTime,_value) values('2023/05/01 06:14',        10)
insert into #test(_DateTime,_value) values('2023/05/01 06:29',        15)
insert into #test(_DateTime,_value) values('2023/05/01 06:44',        21)
insert into #test(_DateTime,_value) values('2023/05/01 06:59',        12)
insert into #test(_DateTime,_value) values('2023/05/01 07:29',        10)
insert into #test(_DateTime,_value) values('2023/05/01 07:44',        17)
insert into #test(_DateTime,_value) values('2023/05/01 08:14',        20)