CODEWITHSUNDEEP
sql-servert-sqlvistadb
oldcoder
oldcoder

Reputation: 342

TSQL - How can I get the total count in a group as part of a subquery

I need to get the total calls per hour per user for the current day, and then calculate the percentage of incoming calls taken by that user. The purpose in the final analysis is to calculate the percentage of a bonus pot per user per hour.

I can get most of the bits, but am stuck calculating the total calls per hour i.e. total calls for each hour group...

Assume the following data (change the date to the current date if necessary):

EnteredBy           EnteredOn
Lisa Scandaleyes    05/07/2017 07:40:04
Fred Smith          05/07/2017 07:54:17
A User              05/07/2017 08:15:06
Johnny Johnson      05/07/2017 08:20:57
A User              05/07/2017 09:27:29
A User              05/07/2017 09:36:16
A User              05/07/2017 09:42:36
A User              05/07/2017 10:09:57

I can easily get the calls per hour:

SELECT DATEPART(HOUR, [EnteredOn]) AS Hour, Count(*) as CallsPerHour
FROM CallHandlingCallData 
WHERE DATEDIFF(d, EnteredOn, GetDate())= 0 
GROUP BY DATEPART(HOUR, [EnteredOn])
ORDER BY Hour;

I can also get the calls per hour per user:

SELECT DATEPART(HOUR, [EnteredOn]) AS Hour, EnteredBy, Count(*) as CallsTaken
FROM CallHandlingCallData 
WHERE DATEDIFF(d,EnteredOn, GetDate())= 0
GROUP BY DATEPART(HOUR, [EnteredOn]), EnteredBy
ORDER BY Hour, EnteredBy;

I can even get almost everything I want EXCEPT that the TotalCalls value and therefore the percentage is based on ALL CALLS for the day:

SELECT Hour, EnteredBy, CallsTaken, TotalCalls, 
CAST(CallsTaken AS Decimal(10,2)) * 100 / CASE TOTALCALLS WHEN 0 THEN 1 ELSE TotalCalls END AS Percentage 
FROM
(
    SELECT DATEPART(HOUR, [EnteredOn]) AS Hour, EnteredBy, Count(*)as CallsTaken, 
    (SELECT Count(*) FROM CallHandlingCallData WHERE DATEDIFF(d, EnteredOn, GetDate())= 0) AS TotalCalls
    FROM CallHandlingCallData
    WHERE DATEDIFF(d,EnteredOn, GetDate())= 0
    GROUP BY DATEPART(HOUR, [EnteredOn]), EnteredBy
) data
ORDER BY Hour, EnteredBy;

This is really close, I just need to get the sub-query to give me the total calls for that hour, instead of for the whole day.

Notes

  1. I am unable to use OVER() as it is not supported by one of the database engines that may be used with the final query.
  2. I would prefer to do such calculations in the UI, but the result has to be output to a spreadsheet removing that option

Any suggestions would be most gratefully received.

Upvotes: 0

Views: 414

Answers (1)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Calculate the group totals separately, then join them up.

SELECT 
    DATEPART(HOUR, C1.EnteredOn) AS [Hour], 
    C1.EnteredBy, 
    COUNT(*) AS CallsTaken, 
    Totals.TotalCalls, 
    CAST(COUNT(*) * 100.0 / Totals.TotalCalls AS DECIMAL(10, 2)) AS [Percentage]
FROM CallHandlingCallData AS C1
JOIN (
    SELECT DATEPART(HOUR, C2.EnteredOn) AS [Hour], COUNT(*) AS TotalCalls
    FROM CallHandlingCallData AS C2
    WHERE DATEDIFF(d, C2.EnteredOn, GetDate()) = 0
    GROUP BY DATEPART(HOUR, C2.EnteredOn)
) Totals ON Totals.[Hour] = DATEPART(HOUR, C1.EnteredOn) 
WHERE DATEDIFF(d, C1.EnteredOn, GetDate()) = 0
GROUP BY 
    DATEPART(HOUR, C1.EnteredOn), 
    C1.EnteredBy, 
    Totals.TotalCalls

If it looks like I'm overdoing it with the aliases: I find it's much better to be safe than sorry when it comes to disambiguating inner and outer column references.

If you need rows with zeroes for hours where nothing happened as well, that's a whole 'nother kettle of fish with UNION ALL and number tables and I'm not going there unless we have to.

Upvotes: 0

Related Questions