SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Time difference between records
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

26 Posts

Posted - 02/12/2014 :  02:32:43  Show Profile  Reply with Quote
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

India
158 Posts

Posted - 02/12/2014 :  03:23:55  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 02/12/2014 :  06:18:08  Show Profile  Reply with Quote

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



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000