Time deduction - 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 2000 Forums

SQL Server Development (2000)

Time deduction

Author

Topic

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2008-11-19 : 05:00:58

I have the following code
SELECT
EAP_STAFF,
MIN (EAP_SIGN_IN_TIME)AS SignIn,
MAX (EAP_SIGN_OUT_TIME)AS SignOut,
EAP_SIGN_OUT_TIME - EAP_SIGN_IN_TIME AS TotalSignedOut

FROM dbo.EVT_AGENT_PERF_BELLS

WHERE EAP_SIGN_IN_TIME >= DATEADD(d, DATEDIFF(d, 0, getdate() -1), 0)
and EAP_SIGN_IN_TIME < DATEADD(d, DATEDIFF(d, 0, getdate()), 0)
and EAP_STAFF = 2800
GROUP BY
EAP_STAFF,
EAP_SIGN_IN_TIME,
EAP_SIGN_OUT_TIME
Order By EAP_SIGN_IN_TIME

That returns the following results
Agent SignIn SignOut TotSignOut
2800 18-11-08 07:45:27 18-11-08 08:17:16 00:31:49
2800 18-11-08 09:21:39 18-11-08 13:29:52 04:08:13

I am looking for the code that I can use to minus the signed out from the next sign in falue if the agent numbers are the same.

Thanks

Whitmoj
If you are in a hurry you will never get there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 06:55:44

do you have a unique valued column in your table?

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2008-11-19 : 07:18:52

Sorry No

Whitmoj
If you are in a hurry you will never get there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 08:51:43

[code]
SELECT
t.EAP_STAFF,
t.EAP_SIGN_IN_TIMEAS SignIn,
t.EAP_SIGN_OUT_TIME AS SignOut,
DATEDIFF(ss,EAP_SIGN_OUT_TIME ,
(SELECT TOP 1 EAP_SIGN_IN_TIME FROM dbo.EVT_AGENT_PERF_BELLS WHERE Agent=t.Agent AND EAP_SIGN_IN_TIME>t.EAP_SIGN_OUT_TIME ORDER BY EAP_SIGN_IN_TIME))
AS TotalSignedOut
FROM dbo.EVT_AGENT_PERF_BELLS t

WHERE t.EAP_SIGN_IN_TIME >= DATEADD(d, DATEDIFF(d, 0, getdate() -1), 0)
and t.EAP_SIGN_IN_TIME < DATEADD(d, DATEDIFF(d, 0, getdate()), 0)
and t.EAP_STAFF = 2800
Order By t.EAP_SIGN_IN_TIME
[/code]

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2008-11-19 : 09:18:45

Cheers

Whitmoj
If you are in a hurry you will never get there

whitmoj
Yak Posting Veteran

68 Posts

Posted - 2008-11-19 : 09:33:44

The code works a treat

Many thanks

Whitmoj
If you are in a hurry you will never get there

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-19 : 10:03:50

cheers