Showing posts with label sqlfunctions. Show all posts
Showing posts with label sqlfunctions. Show all posts

Tuesday, January 10, 2017

Using Lag to get the difference between two rows

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