Find the missing hour with Lag analytic function

Question

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

Answer 1

--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