Rollup Incorrect - One Field
-
I have a query that I am writing to pull status for a phone system.
All of the totals are correct except one. I can't figure out what I'm missing.
Any ideas?DECLARE @tot AS INT,
@a1tot AS INT,
@a2tot AS INT,
@a3tot AS INT,
@a4tot AS INT,
@a5tot AS INT,
@a6tot AS INT,
@a7tot AS INT,
@a8tot AS INT,
@a9tot AS INT,
@a10tot AS INT,
@a11tot AS INT,
@a12tot AS INT,
@jantot AS INT,
@febtot AS INT,
@martot AS INT,
@aprtot AS INT,
@maytot AS INT,
@juntot AS INT,
@jultot AS INT,
@augtot AS INT,
@septot AS INT,
@octtot AS INT,
@novtot AS INT,
@dectot AS INT,
@acd AS INT,
@non AS INT,
@out AS INT,
@avg AS INT;SET @tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016);SET @a2tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent1');SET @a1tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent2');SET @a3tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent3');SET @a4tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent4');SET @a5tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent5');SET @a6tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent6');SET @a7tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent7');SET @a8tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent8');SET @a9tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent9');SET @a10tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent10');SET @a11tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent11');SET @a12tot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Agent_Name = 'Agent12');SET @jantot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 01);SET @febtot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 02);SET @martot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 03);SET @aprtot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 04);SET @maytot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 05);SET @juntot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 06);SET @jultot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 07);SET @augtot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 08);SET @septot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 09);SET @octtot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 10);SET @novtot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 11);SET @dectot = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(MONTH, Call_Start_Time) = 12);SET @acd = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Call_Type LIKE '%Inbound ACD%');SET @non = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Call_Type LIKE '%Inbound Non%');SET @out = (SELECT COUNT(DISTINCT record)
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
AND Call_Type LIKE '%Outbound%');SELECT Agent,
Jan2016A,
Jan2016N,
Jan2016O,
Jan2016T,
REPLACE(CONCAT((Jan2016t * 100) / @jantot, '%'), '100%', ' ') AS 'JanPCT',
Feb2016A,
Feb2016N,
Feb2016O,
Feb2016T,
REPLACE(CONCAT((Feb2016t * 100) / @Febtot, '%'), '100%', ' ') AS 'FebPCT',
Mar2016A,
Mar2016N,
Mar2016O,
Mar2016T,
REPLACE(CONCAT((Mar2016t * 100) / @Martot, '%'), '100%', ' ') AS 'MarPCT',
Apr2016A,
Apr2016N,
Apr2016O,
Apr2016T,
REPLACE(CONCAT((Apr2016t * 100) / @Aprtot, '%'), '100%', ' ') AS 'AprPCT',
May2016A,
May2016N,
May2016O,
May2016T,
REPLACE(CONCAT((May2016t * 100) / @Maytot, '%'), '100%', ' ') AS 'MayPCT',
Jun2016A,
Jun2016N,
Jun2016O,
Jun2016T,
REPLACE(CONCAT((Jun2016t * 100) / @Juntot, '%'), '100%', ' ') AS 'JunPCT',
Jul2016A,
Jul2016N,
Jul2016O,
Jul2016T,
REPLACE(CONCAT((Jul2016t * 100) / @Jultot, '%'), '100%', ' ') AS 'JulPCT',
Aug2016A,
Aug2016N,
Aug2016O,
Aug2016T,
REPLACE(CONCAT((Aug2016t * 100) / @Augtot, '%'), '100%', ' ') AS 'AugPCT',
Sep2016A,
Sep2016N,
Sep2016O,
Sep2016T,
REPLACE(CONCAT((Sep2016t * 100) / @Septot, '%'), '100%', ' ') AS 'SepPCT',
Oct2016A,
Oct2016N,
Oct2016O,
Oct2016T,
REPLACE(CONCAT((Oct2016t * 100) / @octtot, '%'), '100%', ' ') AS 'OctPCT',
Nov2016N,
Nov2016O,
Nov2016T,
REPLACE(CONCAT((Nov2016t * 100) / @novtot, '%'), '100%', ' ') AS 'NovPCT',
Dec2016A,
Dec2016N,
Dec2016O,
Dec2016T,
REPLACE(CONCAT((Dec2016t * 100) / @dectot, '%'), '100%', ' ') AS 'DecPCT',
(Jan2016a + Feb2016a + Mar2016a + Apr2016a + May2016a + Jun2016a + Jul2016a + Aug2016a + Sep2016a + Oct2016a + Nov2016a + Dec2016a) AS ACDTot,
REPLACE(CONCAT(((Jan2016a + Feb2016a + Mar2016a + Apr2016a + May2016a + Jun2016a + Jul2016a + Aug2016a + Sep2016a + Oct2016a + Nov2016a + Dec2016a) * 100) / @acd, '%'), '100%', ' ') AS 'ACDPCT',
(Jan2016n + Feb2016n + Mar2016n + Apr2016n + May2016n + Jun2016n + Jul2016n + Aug2016n + Sep2016n + Oct2016n + Nov2016n + Dec2016n) AS NonTot,
REPLACE(CONCAT(((Jan2016n + Feb2016n + Mar2016n + Apr2016n + May2016n + Jun2016n + Jul2016n + Aug2016n + Sep2016n + Oct2016n + Nov2016n + Dec2016n) * 100) / @non, '%'), '100%', ' ') AS 'NonPCT',
(Jan2016o + Feb2016o + Mar2016o + Apr2016o + May2016o + Jun2016o + Jul2016o + Aug2016o + Sep2016o + Oct2016o + Nov2016o + Dec2016o) AS OutTot,
REPLACE(CONCAT(((Jan2016o + Feb2016o + Mar2016o + Apr2016o + May2016o + Jun2016o + Jul2016o + Aug2016o + Sep2016o + Oct2016o + Nov2016o + Dec2016o) * 100) / @out, '%'), '100%', ' ') AS 'OutPCT',
(Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) AS Total,
REPLACE(CONCAT(((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) * 100) / @tot, '%'), '100%', ' ') AS 'TotPCT',
((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) AS 'TotAvg',
REPLACE(CONCAT(ISNULL(CASE
WHEN Agent = 'Agent1' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent2' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent3' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent4' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent5' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent6' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent7' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent8' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent9' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent10' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent11' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot))
WHEN Agent = 'Agent12' THEN (((((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) * 12) * 100) / (@a2tot + @a1tot + @a3tot + @a4tot + @a5tot + @a6tot + @a7tot + @a8tot + @a9tot + @a10tot + @a11tot + @a12tot)) ELSE NULL
END, 100), '%'), '100%', ' ') AS AvgPCT
FROM (SELECT COALESCE(Agent_Name, 'Totals') AS Agent,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 01 THEN 1 ELSE 0
END) AS Jan2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 01
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Jan2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 01
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Jan2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 01
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Jan2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 02 THEN 1 ELSE 0
END) AS Feb2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 02
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Feb2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 02
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Feb2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 02
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Feb2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 03 THEN 1 ELSE 0
END) AS Mar2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 03
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Mar2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 03
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Mar2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 03
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Mar2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 04 THEN 1 ELSE 0
END) AS Apr2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 04
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Apr2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 04
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Apr2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 04
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Apr2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 05 THEN 1 ELSE 0
END) AS May2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 05
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS May2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 05
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS May2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 05
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS May2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 06 THEN 1 ELSE 0
END) AS Jun2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 06
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Jun2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 06
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Jun2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 06
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Jun2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 07 THEN 1 ELSE 0
END) AS Jul2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 07
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Jul2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 07
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Jul2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 07
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Jul2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 08 THEN 1 ELSE 0
END) AS Aug2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 08
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Aug2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 08
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Aug2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 08
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Aug2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 09 THEN 1 ELSE 0
END) AS Sep2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 09
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Sep2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 09
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Sep2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 09
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Sep2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 10 THEN 1 ELSE 0
END) AS Oct2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 10
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Oct2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 10
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Oct2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 10
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Oct2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 11 THEN 1 ELSE 0
END) AS Nov2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 11
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Nov2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 11
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Nov2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 11
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Nov2016O,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 12 THEN 1 ELSE 0
END) AS Dec2016T,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 12
AND Call_Type LIKE '%Inbound non%' THEN 1 ELSE 0
END) AS Dec2016N,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 12
AND Call_Type LIKE '%Inbound ACD%' THEN 1 ELSE 0
END) AS Dec2016A,
SUM(CASE
WHEN DATEPART(YEAR, Call_Start_Time) = 2016
AND DATEPART(Month, Call_Start_Time) = 12
AND Call_Type LIKE '%Outbound%' THEN 1 ELSE 0
END) AS Dec2016O,
COUNT(DISTINCT DATEPART(MONTH, Call_Start_Time)) AS Ct
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
GROUP BY Agent_Name WITH ROLLUP) AS a;The results are coming out like this:
That number is obviously not correct.
Thanks in advance!
-
Quick question, what is the divisor ct in your TotAvg calculation?
😎((Jan2016t + Feb2016t + Mar2016t + Apr2016t + May2016t + Jun2016t + Jul2016t + Aug2016t + Sep2016t + Oct2016t + Nov2016t + Dec2016t) / ct) AS 'TotAvg' -
@Eirikur - I think the ct comes from COUNT at the bottom of the query:
COUNT(DISTINCT DATEPART(MONTH, Call_Start_Time)) AS Ct
FROM Agent_Details
WHERE DATEPART(YEAR, Call_Start_Time) = 2016
@caldrumr1234 - Not looked into your AvgTot yet, but the base query may need updating:- SET @a2tot and SET @a1tot are probably the wrong way round
- @a3tot refers to agent "Agent3ath", whereas all the rest are of the form "Agent1", "Agent2" etc, and the CASE statement later on uses "Agent3"
Are these intentional?
A set of test data with scripts to create tables would also be very useful - see this link for some good advice on how to aid those who may be willing to aid you:
Forum Etiquette: How to post data/code on a forum to get the best helpForum Etiquette: How to post data/code on a forum to get the best helpMake sure that you include code in the appropriate IFCode tags, e.g.
<your code here>. You can find the IFCode tags under the INSERT options when you are writing a post. -
colin.frame - Monday, February 6, 2017 1:36 AM
That's just me quickly trying to remove people's real names. Sorry about that!
The rest of the code works fine.
Just that one field doesn't roll up properly, which must mean I am doing something wrong.
I just can't figure out what it is. =-( -
Eirikur Eiriksson - Sunday, February 5, 2017 11:28 PM
COUNT(DISTINCT DATEPART(MONTH, Call_Start_Time)) AS Ct
-
caldrumr1234 - Monday, February 6, 2017 8:28 AM
I don't think that you want to use the DISTINCT here, because there are only twelve distinct months.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
You really should be using a reporting tool (like SSRS) to do complicated reporting like this. Most reporting tools can easily handle complex reports such as this.
If you do continue using T-SQL to do these computations, the syntax GROUP BY <field list> WITH ROLLUP has been deprecated in favor of the syntax GROUP BY ROLLUP(<field list>).
Also, you're calling essentially the same block of code multiple times. You would be better off using a CROSSTAB/PIVOT to do these calculations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
drew.allen - Monday, February 6, 2017 9:27 AM
Thanks, I switched to Rollup(field), but that didn't change anything.
Unfortunately, I am not able to use SSRS.
Any idea why my rollup isn't working for that field, or how it can be fixed? -
drew.allen - Monday, February 6, 2017 9:12 AM
I have to use the distinct number of months, or all fields show 1.
This calculation works. -
caldrumr1234 - Monday, February 6, 2017 2:36 PM
Yes, changing the syntax won't change the results. They both do exactly the same thing, but ROLLUP(<fieldlist>) can be used in more places.
Even Excel would be able to produce this report more easily.
It is extremely difficult to troubleshoot a query when you have NO DATA to test against. That is why several people--including me--have a link in their signatures to help people know how to post SAMPLE DATA and EXPECTED RESULTS.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
While Erikur's point didn't end up applying, it did make me think of the way things were being calculated.
I ended up making new variables for the count and yearly total for each agent, and using another CASE statement to divide the yearly total by the number of months individually per agent, or else show the total.
The numbers add up correctly.
Thank you! I appreciate all the ideas.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply