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.
| Author |
Topic |
|
katalystguy
Starting Member
20 Posts |
 Posted - 2012-04-10 : 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
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2012-04-10 : 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)JimEveryday I learn something that somebody else already knew |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2012-04-10 : 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 - 2012-04-10 : 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? |
|
|
|
|
|
|
|
|
Reducing repetition in sql statment