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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WebKill
Starting Member

32 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
169 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
52326 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  
 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.08 seconds. Powered By: Snitz Forums 2000