Time difference - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Time difference

Author

Topic

chsrikanth
Starting Member

6 Posts

Posted - 2011-12-31 : 01:08:54

Sample data

User Action DateTime

Smith inserted 22/7/2010 8:33
Andy inserted 22/7/2010 9:40
Steve collected 22/7/2010 10:01
Smith Update 22/7/2010 10:05
Scott inserted 22/7/2010 10:10
Paul inserted 22/7/2010 10:30
Smith collected 22/7/2010 10:45
Smith collected 22/7/2010 10:50
Smith deleted 22/7/2010 11:15
Smith inserted 22/7/2010 12:15

Desired output

User Action Time TimeDifference

Smith Inserted 22/7/2010 8:33

Smith deleted 22/7/2010 11:15

I need to find the time difference between Inserted(first) and deleted(before next inserted)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-31 : 01:20:42

[code]
;with cte as
(
select *, row_no = row_number() over (partition by [User] order by [DateTime])
from yourtable
where action in ('inserted', 'deleted')
)
select c1.*, diff = datediff(minute, c1.[DateTime], c2.[DateTime])
from cte c1
left join cte c2 on c1.[User] = c2.[User]
and c1.row_no = c2.row_no - 1
order by [DateTime]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

chsrikanth
Starting Member

6 Posts

Posted - 2011-12-31 : 01:32:04

khtan

I need to take a record before 2nd inserted(It may be deleted,updated,collect....)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-12-31 : 01:49:58

[code]
; with cte as
(
select *, row_no = row_number() over (partition by [User] order by [DateTime])
from yourtable
)
select c.[User], c.[action], c.[DateTime], diff = datediff(minute, c.[DateTime], i.[DateTime])
from cte c
outer apply
(
select top 1 [DateTime]
from cte x
where x.[User] = c.[User]
and x.row_no < (select min(row_no) from cte x where x.[User] = c.[User] and x.[action] = 'inserted' and x.row_no > c.row_no)
order by x.row_no desc
) i
where action = 'inserted'
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources