Help with time calculation query please!!

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

Transact-SQL (2000)

Help with time calculation query please!!

Author

Topic

arif101
Starting Member

4 Posts

Posted - 2008-10-16 : 13:23:08

Hi,

I am trying to write a query that will calculate an employee's productivity on the phone. The query I have created is:

SELECT
U.FirstName + ' ' + U.Surname AS 'AgentName',
CASE WHEN UA.Activity = 6 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'NotAvailable',
CASE WHEN UA.Activity = 5 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'WrapUp',
CASE WHEN UA.Activity = 4 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'PreViewing',
CASE WHEN UA.Activity = 3 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Connected',
CASE WHEN UA.Activity = 2 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Calling',
CASE WHEN UA.Activity = 1 THEN SUM(datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Waiting'
FROM
Users U with (nolock)
LEFT JOIN UserActivity UA with (nolock) ON U.OutboundID = UA.UserID
WHERE
U.UserID = 250
AND UA.StartTime >= '15-oct-2008'
AND UA.EndTime < '16-oct-2008'
GROUP BY
U.Firstname + ' ' + U.Surname,
UA.Activity

This seems to work however it puts the results across multiple rows where I need it to be displayed across 1 eg:

User N/A Wrap PreVi Con Call Wait
Username NULL NULL NULL NULL NULL 151
Username NULL NULL NULL 146 NULL NULL
Username NULL 63 NULL NULL NULL NULL
Username 10 NULL NULL NULL NULL NULL

I think that its the grouping of the UA.Activity that is causing it to go across the 4 separate lines however I cannot figure out how to get it all on the 1 line. Can anyone help please???

Thanks
Arif

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-16 : 13:39:05

[code]SELECT
U.FirstName + ' ' + U.Surname AS 'AgentName',
SUM(CASE WHEN UA.Activity = 6 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'NotAvailable',
SUM(CASE WHEN UA.Activity = 5 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'WrapUp',
SUM(CASE WHEN UA.Activity = 4 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'PreViewing',
SUM(CASE WHEN UA.Activity = 3 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Connected',
SUM(CASE WHEN UA.Activity = 2 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Calling',
SUM(CASE WHEN UA.Activity = 1 THEN datediff(minute,UA.StartTime,UA.EndTime)) END AS 'Waiting'FROM
Users U with (nolock)
LEFT JOIN UserActivity UA with (nolock) ON U.OutboundID = UA.UserID
WHERE
U.UserID = 250
AND UA.StartTime >= '15-oct-2008'
AND UA.EndTime < '16-oct-2008'
GROUP BY
U.Firstname + ' ' + U.Surname
,
UA.Activity[/code]

modify like above. remove the striked out code and do modifications as in blue

arif101
Starting Member

4 Posts

Posted - 2008-10-16 : 15:23:08

thanks for this it worked!!

wahoo!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources