CODEWITHSUNDEEP
sqlsql-serversql-server-2008
1Mojojojo1
1Mojojojo1

Reputation: 173

Trying to compare two consecutive records

I am using SQL Server 2008 and I need help in writing a query that compares two consecutive records.

select recordDate 
from SiteHistory 
where siteId = 556145 
  and isRecent = 0 
  and isRunning = 1
order by 
    recordDate DESC

Gives me around 2000 rows that looks like this:

recordDate
-----------------------
2013-05-08 20:04:23.357
2013-05-08 19:45:26.417
2013-05-08 19:30:24.810 
2013-05-08 19:17:22.843
2013-05-08 19:00:16.017
2013-05-08 18:44:14.230
.....
.....

Now I need to compare the date of each row with the next row and count how many times the difference between two consecutive dates is greater than 15mins. This is what I could come up with so far:

;with temp as(
select row_number()over(order by recordDate DESC)as 'row', recordDate 
from SiteHistory 
where siteId = 556145 and isRecent =0 and isRunning=1
)

select COUNT(*) as Count from temp t1
INNER JOIN temp t2 ON t2.row = t1.row+1
where DATEDIFF(mm,t1.recordDate,t2.recordDate)>15

However, this doesn't give me the desired. Please let me know how I can correct this to suit my requirements.

Upvotes: 2

Views: 14372

Answers (3)

Vasanth
Vasanth

Reputation: 1710

Logic of your query is correct, only thing its trying to get date difference in month change it to minutes in 

datediff(minute, t1.RecordDate, t2.RecordDate) > 15

Query:

  ;with temp as(
     select row_number()over(order by recordDate DESC)as 'row', recordDate 
     from SiteHistory 
     where siteId = 556145 and isRecent = 0 and isRunning = 1
  )
  select COUNT(*) as Count from temp t1
  INNER JOIN temp t2 ON t2.row = t1.row+1
  where DATEDIFF(minute, t1.recordDate, t2.recordDate) > 15

Upvotes: 3

Hogan
Hogan

Reputation: 70513

Maybe it is as simple as this:

where ABS(DATEDIFF(minute,t1.recordDate,t2.recordDate))>15

Upvotes: 1

Kevin Suchlicki
Kevin Suchlicki

Reputation: 3145

"mm" gives you the date difference in months

where DATEDIFF(mm,t1.recordDate,t2.recordDate)>15

Replace "mm" with "minute"

where DATEDIFF(minute,t1.recordDate,t2.recordDate)>15

Upvotes: 2

Related Questions