Can this be done? - 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 2005 Forums

Transact-SQL (2005)

Can this be done?

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2007-11-22 : 08:44:29

I have a table as follows:

User | SignIn | SignOut
5163 | 20/08/2006 20:44:07 | NULL
5163 | NULL | 20/08/2006 20:44:10
5162 | 20/08/2006 20:44:25 | NULL
5163 | 20/08/2006 20:54:22 | NULL
5164 | 20/08/2006 20:56:08 | NULL
5164 | NULL | 20/08/2006 21:08:10
5164 | 20/08/2006 21:08:22 | NULL
5163 | NULL | 20/08/2006 21:12:26
5163 | 20/08/2006 21:12:40 | NULL
5162 | NULL | 20/08/2006 21:15:23
5163 | NULL | 20/08/2006 21:15:59
5163 | 20/08/2006 21:16:10 | NULL

I need to create a query that will calculate the time difference between the users SignIn and SignOut times. The problem is that the SignIn and SignOuts are on seperate rows and these rows are not neccesarily inserted straight after each other. Can this be done in reliably in it's present state? Thanks.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-22 : 08:48:58

select Sin.User,Sin.SignIn,Sout.SignOut,datediff(ss,Sin.SignIn,Sout.SignOut)
from YourTab Sin
inner join YourTab Sout
on Sin.User=Sout.User
where Sin.SignIn is not null
and Sout.SignOut is not null

patshaw
Posting Yak Master

177 Posts

Posted - 2007-11-22 : 09:10:53

Hi,

The query I am now using which is based on your suggestion is this:

SET 		dateformat dmy
select 		a.UsersFK, a.SignIn, b.SignOut, datediff(ss,a.SignIn,b.SignOut) as LogInTime
from 		AgentSignInOutLog a
inner join 	AgentSignInOutLog b
on 		a.UsersFK = b.UsersFK
WHERE     	CONVERT(Varchar, a.signin, 103) = CONVERT(varchar, getdate(), 103)
AND 		a.SignIn is not null
AND 		b.SignOut is not null

But this isn't working. The SignOut date is always earlier than the SignIn date and hence gives a negative number for the time difference. It is also pulling 75k rows using the above when itonly be about 180.

Pat.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 09:15:43

Is the SignIn and SignOut always in pairs ?

How about cross midnight situation, how are you going to handle that ?

What is the expected result from the sample data ?

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

patshaw
Posting Yak Master

177 Posts

Posted - 2007-11-22 : 09:21:45

If you mean do the rows order logically in the table then no, they are not always in pairs.

Cross midnight will not be an issue as there will always be a SignOut before this time

I just need to calculate the difference in seconds between each SignIn and its nearest (Time) SignOut where the AgentFK matches.

Thanks.

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-11-22 : 09:25:14

Yes, I guess the problem is that one user can log in multiple times during the day.
Do you have any identifiers that binds together such log ins and outs?
Is it possible that the same user logs in twice from different computers?

Example:
user 1, login in at 3pm, logs out at 5pm
user 1 (on another computer) logs in at 4pm, logs out at 6pm

So, is it
3pm - 5pm, 4pm - 6pm
OR
3pm - 6pm, 4pm-5pm
???

I provided a query for your sample data, the most simple case - only one login.

patshaw
Posting Yak Master

177 Posts

Posted - 2007-11-22 : 09:34:13

This is the problem. There are no other identifiers that I can see to distinguish the logins.

A user should not be able to log in twice on two different machines, I can vouch for that. What I ideally need to do is grab the SignOut that is nearest in time to the SignIn. So, for every SignIn, the Signout that is the nearest in time is the correct SignOut. This is helped by the fact that a user cannot be signed in twice (ie. on different machines) with only one SignOut.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 09:38:44

[code]DECLARE @sample TABLE
(
[User] int,
SignIn datetime,
SignOut datetime
)
SET dateformat dmy
INSERT INTO @sample
SELECT 5163 , '20/08/2006 20:44:07' , NULL UNION ALL
SELECT 5163 , NULL , '20/08/2006 20:44:10' UNION ALL
SELECT 5162 , '20/08/2006 20:44:25' , NULL UNION ALL
SELECT 5163 , '20/08/2006 20:54:22' , NULL UNION ALL
SELECT 5164 , '20/08/2006 20:56:08' , NULL UNION ALL
SELECT 5164 , NULL , '20/08/2006 21:08:10' UNION ALL
SELECT 5164 , '20/08/2006 21:08:22' , NULL UNION ALL
SELECT 5163 , NULL , '20/08/2006 21:12:26' UNION ALL
SELECT 5163 , '20/08/2006 21:12:40' , NULL UNION ALL
SELECT 5162 , NULL , '20/08/2006 21:15:23' UNION ALL
SELECT 5163 , NULL , '20/08/2006 21:15:59' UNION ALL
SELECT 5163 , '20/08/2006 21:16:10' , NULL

SELECT [User], SignIn, SignOut, TimeDiff = DATEDIFF(minute, SignIn, SignOut)
FROM
(
SELECT [User], SignIn,
SignOut = (SELECT MIN(SignOut) FROM @sample x WHERE x.[User] = s.[User] AND x.SignOut >= s.SignIn)
FROM @sample s
WHERE SignIn IS NOT NULL
) d
ORDER BY [User], SignIn
/*
User SignIn SignOut TimeDiff
----------- ------------------------ ------------------------- -----------
5162 2006-08-20 20:44:25.000 2006-08-20 21:15:23.000 31
5163 2006-08-20 20:44:07.000 2006-08-20 20:44:10.000 0
5163 2006-08-20 20:54:22.000 2006-08-20 21:12:26.000 18
5163 2006-08-20 21:12:40.000 2006-08-20 21:15:59.000 3
5163 2006-08-20 21:16:10.000 NULL NULL
5164 2006-08-20 20:56:08.000 2006-08-20 21:08:10.000 12
5164 2006-08-20 21:08:22.000 NULL NULL

(7 row(s) affected)
*/[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 09:45:15

Another method

DECLARE @sample TABLE
(
	[User]	int,
	SignIn	datetime,
	SignOut	datetime
)
SET dateformat dmy
INSERT INTO @sample
SELECT	5163 , '20/08/2006 20:44:07' , 	NULL			UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 20:44:10'	UNION ALL
SELECT	5162 , '20/08/2006 20:44:25' , 	NULL			UNION ALL
SELECT	5163 , '20/08/2006 20:54:22' , 	NULL			UNION ALL
SELECT	5164 , '20/08/2006 20:56:08' , 	NULL			UNION ALL
SELECT	5164 , NULL , 			'20/08/2006 21:08:10'	UNION ALL
SELECT	5164 , '20/08/2006 21:08:22' , 	NULL			UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 21:12:26'	UNION ALL
SELECT	5163 , '20/08/2006 21:12:40' , 	NULL			UNION ALL
SELECT	5162 , NULL , 			'20/08/2006 21:15:23'	UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 21:15:59'	UNION ALL
SELECT	5163 , '20/08/2006 21:16:10' , 	NULL

SELECT	[User], SignIn, SignOut = MIN(SignOut), 
	TimeDiff = DATEDIFF(minute, SignIn, MIN(SignOut))
FROM
(
	SELECT	si.[User], si.[SignIn], so.SignOut
	FROM	@sample si left JOIN @sample so
		ON	si.[User]	= so.[User]
		AND	so.SignOut	IS NOT NULL
		AND	si.SignIn	<= so.SignOut
	WHERE	si.SignIn	IS NOT NULL
) a
GROUP BY [User], SignIn
ORDER BY [User], SignIn

/*
User        SignIn                   SignOut                   TimeDiff    
----------- ------------------------ ------------------------- ----------- 
       5162 2006-08-20 20:44:25.000  2006-08-20 21:15:23.000            31 
       5163 2006-08-20 20:44:07.000  2006-08-20 20:44:10.000             0 
       5163 2006-08-20 20:54:22.000  2006-08-20 21:12:26.000            18 
       5163 2006-08-20 21:12:40.000  2006-08-20 21:15:59.000             3 
       5163 2006-08-20 21:16:10.000  NULL                      NULL
       5164 2006-08-20 20:56:08.000  2006-08-20 21:08:10.000            12 
       5164 2006-08-20 21:08:22.000  NULL                      NULL

(7 row(s) affected)
*/

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-22 : 09:52:59

Another method

DECLARE @sample TABLE
(
	[User]	int,
	SignIn	datetime,
	SignOut	datetime
)
SET dateformat dmy
INSERT INTO @sample
SELECT	5163 , '20/08/2006 20:44:07' , 	NULL			UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 20:44:10'	UNION ALL
SELECT	5162 , '20/08/2006 20:44:25' , 	NULL			UNION ALL
SELECT	5163 , '20/08/2006 20:54:22' , 	NULL			UNION ALL
SELECT	5164 , '20/08/2006 20:56:08' , 	NULL			UNION ALL
SELECT	5164 , NULL , 			'20/08/2006 21:08:10'	UNION ALL
SELECT	5164 , '20/08/2006 21:08:22' , 	NULL			UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 21:12:26'	UNION ALL
SELECT	5163 , '20/08/2006 21:12:40' , 	NULL			UNION ALL
SELECT	5162 , NULL , 			'20/08/2006 21:15:23'	UNION ALL
SELECT	5163 , NULL , 			'20/08/2006 21:15:59'	UNION ALL
SELECT	5163 , '20/08/2006 21:16:10' , 	NULL

SELECT	[User], SignIn, SignOut, TimeDiff = DATEDIFF(minute, SignIn, SignOut)
FROM
(
	SELECT	[User], 
		SignIn  = MAX(CASE WHEN RowNo % 2 = 1 THEN SignIn END),
		SignOut = MAX(CASE WHEN RowNo % 2 = 0 THEN SignOut END)
	FROM
	(
		SELECT	[User], SignIn, SignOut, 
			RowNo = row_number() OVER (PARTITION BY [User] ORDER BY [User], coalesce(SignIn, SignOut))
		FROM	@sample
	) d
	GROUP BY [User], (RowNo + 1) / 2
) d
ORDER BY [User]

/*
User        SignIn                   SignOut                   TimeDiff    
----------- ------------------------ ------------------------- ----------- 
       5162 2006-08-20 20:44:25.000  2006-08-20 21:15:23.000            31 
       5163 2006-08-20 20:44:07.000  2006-08-20 20:44:10.000             0 
       5163 2006-08-20 20:54:22.000  2006-08-20 21:12:26.000            18 
       5163 2006-08-20 21:12:40.000  2006-08-20 21:15:59.000             3 
       5163 2006-08-20 21:16:10.000  NULL                      NULL
       5164 2006-08-20 20:56:08.000  2006-08-20 21:08:10.000            12 
       5164 2006-08-20 21:08:22.000  NULL                      NULL

(7 row(s) affected)
*/

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

patshaw
Posting Yak Master

177 Posts

Posted - 2007-11-22 : 10:12:40

Khtan,

Once again many thanks. This does exactly what I need.

Thanks to all for your help.

Pat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources