| Author |
 Topic  |
|
|
katalystguy
Starting Member
20 Posts |
 Posted - 04/10/2012 : 09:54:42
|
Hi,
I have a statement (below) which works fine but for each case statement line the TalkTime column (datetime) is converted into seconds. Is there a way of calculating the talktime on seconds once and then testing this multiple times to get my counts?
SELECT
SUM (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) as AHT_Seconds,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 0 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 60 THEN 1 ELSE 0 END) AS AHT_Count_1_Minute,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 60 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 120 THEN 1 ELSE 0 END) AS AHT_Count_2_Minutes,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 120 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 180 THEN 1 ELSE 0 END) AS AHT_Count_3_Minutes,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 180 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 240 THEN 1 ELSE 0 END) AS AHT_Count_4_Minutes,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 240 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 300 THEN 1 ELSE 0 END) AS AHT_Count_5_Minutes,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 300 AND (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) < 360 THEN 1 ELSE 0 END) AS AHT_Count_6_Minutes,
SUM (CASE WHEN (DATEPART(hour, TalkTime) * 3600 + DATEPART(minute, TalkTime) * 60 + DATEPART(second, TalkTime)) >= 360 THEN 1 ELSE 0 END) AS AHT_Count_6_Plus_Minutes
FROM Import_ACD_Call_Details WHERE Convert(varchar(10), SEGStart, 103) = Convert(varchar(10), ?, 103) AND ANSLogin = ? AND FirstVDN IN(SELECT VDN from VDN_List WHERE LoB = ?)
Cheers. |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 04/10/2012 : 10:19:47
|
This might help, as it looks like what you are doing is just converting a datetime to seconds.
This gives you the seconds since midnight
declare @date datetime
set @date = current_timestamp
select datediff(s,dateadd(day,datediff(day,0,@date),0),@date)
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 04/10/2012 : 11:52:42
|
To further reduce it:
1) Since each CASE is evaluated top to bottom, the leftmost expression is redundant and can be removed.
2) You can evaluate the DATEDIFF/DATEADD expression once by using a common-table expression (CTE) and then simplify the summary query.
Here's one version:WITH CTE AS (SELECT DATEDIFF(s,DATEADD(day,DATEDIFF(day,0,TalkTime),0),TalkTime) as AHT_Seconds
FROM Import_ACD_Call_Details
WHERE Convert(varchar(10), SEGStart, 103) = Convert(varchar(10), ?, 103) AND ANSLogin = ?
AND FirstVDN IN(SELECT VDN from VDN_List WHERE LoB = ?))
SELECT SUM (AHT_Seconds) as AHT_Seconds,
SUM (CASE WHEN AHT_Seconds < 60 THEN 1 ELSE 0 END) AS AHT_Count_1_Minute,
SUM (CASE WHEN AHT_Seconds < 120 THEN 1 ELSE 0 END) AS AHT_Count_2_Minutes,
SUM (CASE WHEN AHT_Seconds < 180 THEN 1 ELSE 0 END) AS AHT_Count_3_Minutes,
SUM (CASE WHEN AHT_Seconds < 240 THEN 1 ELSE 0 END) AS AHT_Count_4_Minutes,
SUM (CASE WHEN AHT_Seconds < 300 THEN 1 ELSE 0 END) AS AHT_Count_5_Minutes,
SUM (CASE WHEN AHT_Seconds < 360 THEN 1 ELSE 0 END) AS AHT_Count_6_Minutes,
SUM (CASE WHEN AHT_Seconds >= 360 THEN 1 ELSE 0 END) AS AHT_Count_6_Plus_Minutes
FROM CTE |
|
|
|
katalystguy
Starting Member
20 Posts |
Posted - 04/10/2012 : 12:40:40
|
Hi,
Unfortunately that did not work out too well, for the two matching records tested I got a count of 2 in all the minute columns apart from AHT_Count_6_Plus_Minutes. I think the problem might be that column AHT_Seconds is tested rather than variable AHT_Seconds?
|
|
|
| |
Topic |
|
|
|
Reducing repetition in sql statment
