locked
SQL to Capture missing timestamps RRS feed

  • Question

  • I have a table in the warehouse that is expected to capture data every minute constantly and I need to report if there is data missing for any minute. It is a "datetime" column. I need a SQL statement that will return me the minutes(datetimestamp) that are missed for the past 1 hour. 

    Using SQL server 2012. Need help with the SQL query. Thanks in advance.


    svk

    Monday, July 23, 2018 7:25 PM

Answers

  • Generate a table with last 60 minutes and use your table  datetime column (convert it to the beginning of minute) and LEFT JOIN with this minutes list table and check your datetime column for NULLs.

    Here is the example:

    declare @Param int=60
    ;with num1 as (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(minute, -n+1, getdate() ) dt  
    from(Select n from nums) D(n)
    )
    select Dateadd(minute,DATEDIFF(minute,0,dt),0) dt from dates
    
    
    ---you need to do the same for your table's datetime column before LEFT JOIN 
    
    Dateadd(minute,DATEDIFF(minute,0,yourdtcolumn),0)

    • Marked as answer by czarvk Monday, July 23, 2018 8:16 PM
    Monday, July 23, 2018 7:54 PM
  • you can use a logic like this
    SELECT v.number AS MinutesMissed
    FROM master..spt_values v
    OUTER APPLY
    (
    SELECT COUNT(1) AS Cnt
    FROM YourTable WHERE datetimecolumn >= DATEADD(hh,DATEDIFF(hh,0,GETDATE())-1,0)
    AND datetimecolumn < DATEADD(hh,DATEDIFF(hh.0,GETDATE()),0)
    AND DATEPART(minute,datetimecolumn) = v.number
    )t
    WHERE v.type = 'p'
    AND v.number BETWEEN 0 AND 59
    AND COALESCE(Cnt,0) = 0
    ORDER BY v.number


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by czarvk Monday, July 23, 2018 8:30 PM
    Monday, July 23, 2018 8:17 PM

All replies

  • Generate a table with last 60 minutes and use your table  datetime column (convert it to the beginning of minute) and LEFT JOIN with this minutes list table and check your datetime column for NULLs.

    Here is the example:

    declare @Param int=60
    ;with num1 as (
    SELECT 1 as n
    UNION ALL SELECT n+1 as n
    FROM Num1 Where n <101), 
    Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1
    WHere n<= @Param ) ---Control the total columns list
    ,dates as (
    Select n, DATEADD(minute, -n+1, getdate() ) dt  
    from(Select n from nums) D(n)
    )
    select Dateadd(minute,DATEDIFF(minute,0,dt),0) dt from dates
    
    
    ---you need to do the same for your table's datetime column before LEFT JOIN 
    
    Dateadd(minute,DATEDIFF(minute,0,yourdtcolumn),0)

    • Marked as answer by czarvk Monday, July 23, 2018 8:16 PM
    Monday, July 23, 2018 7:54 PM
  • you can use a logic like this
    SELECT v.number AS MinutesMissed
    FROM master..spt_values v
    OUTER APPLY
    (
    SELECT COUNT(1) AS Cnt
    FROM YourTable WHERE datetimecolumn >= DATEADD(hh,DATEDIFF(hh,0,GETDATE())-1,0)
    AND datetimecolumn < DATEADD(hh,DATEDIFF(hh.0,GETDATE()),0)
    AND DATEPART(minute,datetimecolumn) = v.number
    )t
    WHERE v.type = 'p'
    AND v.number BETWEEN 0 AND 59
    AND COALESCE(Cnt,0) = 0
    ORDER BY v.number


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Marked as answer by czarvk Monday, July 23, 2018 8:30 PM
    Monday, July 23, 2018 8:17 PM