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
This blog is created to add all the information and experiences that I have learnt while working on MS sql server as a DBA. The blog is also updated frequently with the real world problem that I encounter hands on at work and the resolutions to resolve them. I hope this blog will be of some use to you and you will revisit. Thank you for stopping by and you are welcome to leave comments.
Showing posts with label sqlfunctions. Show all posts
Showing posts with label sqlfunctions. Show all posts
Tuesday, January 10, 2017
Subscribe to:
Posts (Atom)