Better Practices for writing this query?

Question

Better Practices for writing this query?

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 1

 FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
 JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
   ON com.[campaign] = ud.[campaign]
CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec
CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec

DaveBriCam SSCarpal Tunnel Points: 4294 More actions · Answer 2

Hi Scott.... revisiting your code above.... I'm getting a "Msg 8120, Level 16, State 1, Line 24

Column 'alias1.CALL_TIME_SECS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

When I try this below which so looks like it will work:

SELECT [CALL_ID], [DATE],
CALL_TIME_SECS AS [CALL TIME (secs)],
TALK_TIME_SECS AS [TALK TIME (secs)],
CALL_TIME_SECS - TALK_TIME_SECS AS [diff],

CASE 
WHEN CALL_TIME_SECS - TALK_TIME_SECS <= 9 then '1'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 10 AND 59 then '2'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 60 AND 179 then '3'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 180 AND 299 then '4'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 300 AND 599 then '5'
WHEN CALL_TIME_SECS - TALK_TIME_SECS >= 600 then '6' else 0 end AS [ANALYSIS],


CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS <= 9 
then COUNT(DISTINCT [DISPOSITION]) else '' end as [< 9 secs],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 10 AND 59 
then COUNT(DISTINCT [DISPOSITION]) else '' end as [< 1 Min],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 60 AND 179 
then count(DISTINCT [DISPOSITION]) else '' end as [1-3 MINUTES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 180 AND 299 
then count(DISTINCT [DISPOSITION]) else '' end as [3 - 5 MINTUES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 300 AND 599 
then count(DISTINCT [DISPOSITION]) else '' end as [5 - 10 MINUTES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS >= 600 
then count(DISTINCT [DISPOSITION]) else '' end as [10+ MINUTES],

[AGENT], [DISPOSITION], com.[CAMPAIGN]
FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
ON com.[campaign] = ud.[campaign]
CROSS APPLY (
    SELECT dbo.fnGetSeconds(CALL_TIME) AS CALL_TIME_SECS,
    dbo.fnGetSeconds(TALK_TIME) AS TALK_TIME_SECS    
) AS alias1

WHERE (ud.[Client] Like 'Better%')  AND com.DATE >= '2021-01-01' AND com.DATE <= '2021-03-1'
and ([disposition] like 'Live Transfer%' OR [DISPOSITION] LIKE 'Transfer Made%'
OR [DISPOSITION] = 'Transferred to 3rd Party') AND [DOMAIN] = '05'

group by [agent], [DISPOSITION], [CALL_ID], [date], [call_time], [TALK_TIME], com.[CAMPAIGN]
order by [< 9 secs], [< 1 Min], [1-3 MINUTES], [3 - 5 MINTUES], [5 - 10 MINUTES], [10+ MINUTES] desc

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

DaveBriCam wrote:

Hi Scott.... revisiting your code above.... I'm getting a "Msg 8120, Level 16, State 1, Line 24 Column 'alias1.CALL_TIME_SECS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

When I try this below which so looks like it will work:

SELECT [CALL_ID], [DATE],
CALL_TIME_SECS AS [CALL TIME (secs)],
TALK_TIME_SECS AS [TALK TIME (secs)],
CALL_TIME_SECS - TALK_TIME_SECS AS [diff],

CASE 
WHEN CALL_TIME_SECS - TALK_TIME_SECS <= 9 then '1'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 10 AND 59 then '2'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 60 AND 179 then '3'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 180 AND 299 then '4'
WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 300 AND 599 then '5'
WHEN CALL_TIME_SECS - TALK_TIME_SECS >= 600 then '6' else 0 end AS [ANALYSIS],


CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS <= 9 
then COUNT(DISTINCT [DISPOSITION]) else '' end as [< 9 secs],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 10 AND 59 
then COUNT(DISTINCT [DISPOSITION]) else '' end as [< 1 Min],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 60 AND 179 
then count(DISTINCT [DISPOSITION]) else '' end as [1-3 MINUTES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 180 AND 299 
then count(DISTINCT [DISPOSITION]) else '' end as [3 - 5 MINTUES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS BETWEEN 300 AND 599 
then count(DISTINCT [DISPOSITION]) else '' end as [5 - 10 MINUTES],
CASE WHEN CALL_TIME_SECS - TALK_TIME_SECS >= 600 
then count(DISTINCT [DISPOSITION]) else '' end as [10+ MINUTES],

[AGENT], [DISPOSITION], com.[CAMPAIGN]
FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
ON com.[campaign] = ud.[campaign]
CROSS APPLY (
    SELECT dbo.fnGetSeconds(CALL_TIME) AS CALL_TIME_SECS,
    dbo.fnGetSeconds(TALK_TIME) AS TALK_TIME_SECS    
) AS alias1

WHERE (ud.[Client] Like 'Better%')  AND com.DATE >= '2021-01-01' AND com.DATE <= '2021-03-1'
and ([disposition] like 'Live Transfer%' OR [DISPOSITION] LIKE 'Transfer Made%'
OR [DISPOSITION] = 'Transferred to 3rd Party') AND [DOMAIN] = '05'

group by [agent], [DISPOSITION], [CALL_ID], [date], [call_time], [TALK_TIME], com.[CAMPAIGN]
order by [< 9 secs], [< 1 Min], [1-3 MINUTES], [3 - 5 MINTUES], [5 - 10 MINUTES], [10+ MINUTES] desc

You just need to add it to the group by

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

DaveBriCam wrote:

How do I integrate your
CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec
code with the needed code below?:
FROM a2wh.dbo.CallLogCommon com with (NOLOCK)
JOIN a2wh.dbo.Campaigns ud with (NOLOCK)
ON com.[campaign] = ud.[campaign]

If you look at my code example, I already did that.

You also haven't answered any of my questions so that we can seriously simplify the rest of the code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)