t-sqlanalytic-functions

Find the missing hour with Lag analytic function


I have to find the missing hour in my table , for frequency = 1 I have to find a record per hour, if it's not the case, I have to display the missing hour. here's my code

declare @StartDate datetime declare @EndDate datetime declare @now datetime set @now = getdate() set @StartDate = dateadd(day,-30,@now) set @EndDate = dateadd(day,-2,@now) Select Flow.Id,Flow.ComponentId, Frequency.Name frequencyName, Flow.MeasurementDate as MeasurementDate, LAG(MeasurementDate) OVER (ORDER BY MeasurementDate) LagValue, abs(  DATEDIFF (hour, MeasurementDate, LAG(MeasurementDate) OVER (ORDER BY MeasurementDate) ) ) DifferenceDate ,  (CASE WHEN  DATEDIFF (hour, MeasurementDate, LAG(MeasurementDate) OVER (ORDER BY MeasurementDate) )  > '1' THEN 'Yes' ELSE 'No' END) AS  Gap into #tab1  FROM Data.dbo.Flow inner join Data.dbo.Component  on flow.ComponentId = Component.Id  inner join Data.dbo.Frequency on Flow.Frequency = Frequency.Id Where flow.LoaderCode='TOT' and Flow.Frequency='1' and ScheduledVolume IS NOT NULL and MeasurementDate between @StartDate and @EndDate  --and  DATEDIFF (hour, MeasurementDate, LAG(MeasurementDate) OVER (ORDER BY MeasurementDate) ) >1 Group By Frequency.Name, Flow.MeasurementDate, Flow.ComponentId select * from #tab1

Solution

  • --if i right understood then try this
    
        DECLARE @StartDate DATETIME
    DECLARE @EndDate DATETIME
    DECLARE @now DATETIME
    IF OBJECT_ID('Tempdb..#tab1') IS NOT NULL
        BEGIN   
            DROP TABLE #tab1
        END 
    
    SET @now = GETDATE()
    SET @StartDate = GETDATE() - 30
    SET @EndDate = GETDATE() - 2
    
    
    SELECT  Flow.Id ,
            Flow.ComponentId ,
            Frequency.Name AS frequencyName ,
            CONVERT(DATE, Flow.MeasurementDate) AS [Measurement Date] ,
            DATEPART(HOUR, Flow.MeasurementDate) AS [Measurement Hour] ,
            COALESCE(LAG(DATEPART(HOUR, Flow.MeasurementDate)) OVER ( PARTITION BY CONVERT(DATE, MeasurementDate) ORDER BY DATEPART(HOUR,
                                                                  MeasurementDate) ),
                     0) AS [Measurement Previous Hour]
    INTO    #tab1
    FROM    Data.dbo.Flow
            INNER JOIN Data.dbo.Component ON Flow.ComponentId = Component.Id
            INNER JOIN Data.dbo.Frequency ON Flow.Frequency = Frequency.Id
    WHERE   Flow.LoaderCode = 'TOT'
            AND Flow.Frequency = '1'
            AND ScheduledVolume IS NOT NULL
            AND CONVERT(DATE, MeasurementDate) BETWEEN CONVERT(DATE, @StartDate)
                                               AND     CONVERT(DATE, @EndDate)
    
    SELECT  T.* ,
            CASE WHEN ( T.[Measurement Hour] - T.[Measurement Previous Hour] ) > 1
                 THEN ( T.[Measurement Hour] - T.[Measurement Previous Hour] - 1 )
                 ELSE 0
            END AS [Missing Hours]
    FROM    #tab1a AS T
    WHERE   ( T.[Measurement Hour] - T.[Measurement Previous Hour] ) > 1