I used the following query to find how name rows were there in a table in a given date where the time captured in a column were more than one minute between two consecutive rows
--using lag
declare @startdate date ,@enddate date
set @startdate = '01/09/2017'
set @enddate = '01/09/2017'
SELECT *
FROM (
SELECT LogId, lag(PackageStep) over (order by packagename) Over1minPackagestep,
LAG(starttime) OVER (ORDER BY starttime)
StepStartTime ,starttime as StepEndTime, HospitalCode
FROM Package_RunHistory where cast(starttime as date) between @startdate and @enddate
) q
WHERE StepStartTime IS NOT NULL
and DATEDIFF(minute, StepStartTime, StepEndTime) > 1
and clientcode is not null
No comments:
Post a Comment