Time difference between records

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 between records

Author

Topic

WebKill
Starting Member

32 Posts

Posted - 2014-02-12 : 02:32:43

I have done some research and found some examples, I think I am close but cannot pin this down. I need to find the tiem difference between start and end records, but currently it finds a match for each start record to every end record after it instead of 1 to 1.

Example:

DECLARE @DataTable TABLE
(
ActivityTime DATETIME,
ActivityAction VARCHAR(10),
UserName VARCHAR(10)
)

INSERT INTO @DataTable VALUES('2014-02-10 11:18:30.767', 'Open', 'BOB'), ('2014-02-10 11:18:30.883', 'Close', 'BOB'),
('2014-02-10 11:19:19.557', 'Open', 'BOB'), ('2014-02-10 11:19:19.667', 'Close', 'BOB'),
('2014-02-10 11:19:56.190', 'Open', 'JIM'), ('2014-02-10 11:19:56.297', 'Close', 'JIM'),
('2014-02-10 11:21:31.213', 'Open', 'BOB'), ('2014-02-10 11:21:32.133', 'Close', 'BOB')

SELECT Start_Log.ActivityTime , End_Log.ActivityTime, DATEDIFF(MILLISECOND,Start_Log.ActivityTime, End_Log.ActivityTime) as 'Time Difference' ,Start_Log.UserName from @DataTable as Start_Log join @DataTable End_Log
ON Start_Log.UserName = End_Log.UserName
WHERE Start_Log.ActivityTime < End_Log.ActivityTime
AND Start_Log.ActivityAction = 'Open'
AND End_Log.ActivityAction = 'Close'

Currently it gives this:


2014-02-10 11:18:30.767 | 2014-02-10 11:18:30.883 | 116      | BOB
2014-02-10 11:18:30.767 | 2014-02-10 11:19:19.667 | 48900    | BOB
2014-02-10 11:19:19.557 | 2014-02-10 11:19:19.667 | 110      | BOB
2014-02-10 11:19:56.190 | 2014-02-10 11:19:56.297 | 106      | JIM
2014-02-10 11:18:30.767 | 2014-02-10 11:21:32.133 | 181366   | BOB
2014-02-10 11:19:19.557 | 2014-02-10 11:21:32.133 | 132576   | BOB
2014-02-10 11:21:31.213 | 2014-02-10 11:21:32.133 | 920      | BOB

I need it to give this:


2014-02-10 11:18:30.767 | 2014-02-10 11:18:30.883 | 116      | BOB
2014-02-10 11:19:19.557 | 2014-02-10 11:19:19.667 | 110      | BOB
2014-02-10 11:19:56.190 | 2014-02-10 11:19:56.297 | 106      | JIM
2014-02-10 11:21:31.213 | 2014-02-10 11:21:32.133 | 920      | BOB

VeeranjaneyuluAnnapureddy
Posting Yak Master

169 Posts

Posted - 2014-02-12 : 03:23:55

SELECT Start_Log.ActivityTime , End_Log.ActivityTime, DATEDIFF(MILLISECOND,Start_Log.ActivityTime, End_Log.ActivityTime) as 'Time Difference' ,Start_Log.UserName from @DataTable as Start_Log join @DataTable End_Log
ON Start_Log.UserName = End_Log.UserName
WHERE
Start_Log.ActivityAction = 'Open'
AND End_Log.ActivityAction = 'Close'
AND LEFT( RIGHT(CONVERT(VARCHAR,Start_Log.ActivityTime ,108),8),5) = LEFT( RIGHT(CONVERT(VARCHAR,End_Log.ActivityTime ,108),8),5)

Veera

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 06:18:08

[code]
DECLARE @DataTable TABLE
(
ActivityTime DATETIME,
ActivityAction VARCHAR(10),
UserName VARCHAR(10)
)

INSERT INTO @DataTable VALUES('2014-02-10 11:18:30.767', 'Open', 'BOB'), ('2014-02-10 11:18:30.883', 'Close', 'BOB'),
('2014-02-10 11:19:19.557', 'Open', 'BOB'), ('2014-02-10 11:19:19.667', 'Close', 'BOB'),
('2014-02-10 11:19:56.190', 'Open', 'JIM'), ('2014-02-10 11:19:56.297', 'Close', 'JIM'),
('2014-02-10 11:21:31.213', 'Open', 'BOB'), ('2014-02-10 11:21:32.133', 'Close', 'BOB')

;With CTE
AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY UserName,ActivityAction ORDER BY ActivityTime) AS Seq,*
FROM @DataTable
)

SELECT c1.ActivityTime,c2.ActivityTime,DATEDIFF(MILLISECOND,c1.ActivityTime,c2.ActivityTime) AS Diff,c1.UserName
FROM CTE c1
JOIN CTE C2
ON c2.UserName = c1.UserName
AND c2.Seq = c1.Seq
AND c2.ActivityAction = 'Close'
AND c1.ActivityAction = 'Open'

output
----------------------------------------------------------------
ActivityTime ActivityTime Diff UserName
----------------------------------------------------------------
2014-02-10 11:18:30.767 2014-02-10 11:18:30.883 116 BOB
2014-02-10 11:19:19.557 2014-02-10 11:19:19.667 110 BOB
2014-02-10 11:21:31.213 2014-02-10 11:21:32.133 920 BOB
2014-02-10 11:19:56.190 2014-02-10 11:19:56.297 106 JIM

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources