I am trying to get my query to output a row of performance results for each day in the range I am pulling. I keep getting about 10 rows per rep, per day with strange looking results. Would you be able to verify how I can alter my query to break down the results to a day by day reading? Sorry for the trouble...let me know if I should word this differently.
Here is the code:
DECLARE @StartDate AS SMALLDATETIME
DECLARE @EndDate AS SMALLDATETIME
SET @StartDate = '2012-04-01'
SET @EndDate = '2012-04-30'
SET ANSI_WARNINGS OFF
SELECT
DT.DateFull,
--DATEPART(MM,DT.DateFull) AS Date,
--DATENAME(MM,DT.DateFull) AS [Month],
E.AreaID,
--E.GeographicLocationID,
E.EmployeeID,
ES.PeopleSoftTitleStartDate,
QA.TopRep,
QA.RepCount,
CASE
WHEN SUM(QA.TopRep) = '0' OR SUM(RepCount) = '0'
THEN '0'
ELSE CAST(100*(SUM(QA.TopRep))/SUM(QA.RepCount) AS DECIMAL (8,2))
END 'ERP',
QA.RepResolve,
QA.RepResolveCount,
CASE
WHEN SUM(QA.RepResolve) = '0' OR SUM(RepResolveCount) = '0'
THEN '0'
ELSE CAST(100*(SUM(QA.RepResolve))/SUM(QA.RepResolveCount) AS DECIMAL (8,2))
END 'RR',
QA.ACSNPSPromoter,
QA.ACSNPSDetractor,
QA.ACSNPSCount,
CASE
WHEN QA.ACSNPSCount = '0'
THEN '0'
ELSE CAST(100*(SUM(QA.ACSNPSPromoter)-SUM(QA.ACSNPSDetractor))/SUM(QA.ACSNPSCount) AS DECIMAL(8,2))
END 'NPS',
AHT.AHT,
AHT.TalkTime,
AHT.CallWorkTime,
AHT.HandledCalls/*,
CASE
WHEN (DT.DateFull Between emp.Transition_Date and emp.Month_one)
THEN 'First 30 Days'
WHEN (DT.DateFull Between emp.Month_one And emp.Month_Two )
THEN '30 - 60 days'
WHEN (DT.DateFull Between emp.Month_Two And emp.Month_Three)
THEN '60-90 days'
ELSE 'After Period'
END AS Stat_Period*/
INTO #DATA
FROM
dbTools.Config.vwDate AS DT WITH(NOLOCK)
INNER JOIN
dbEmployee.Summary.vwEmployeeHistory AS E WITH(NOLOCK)
ON
DT.datefull BETWEEN E.StartDate AND E.EndDate
LEFT JOIN
dbEmployee.Summary.vwEmployeeSnapshot AS ES WITH(NOLOCK)
ON
E.EmployeeID = ES.EmployeeID
AND DT.datefull BETWEEN E.StartDate AND E.EndDate
LEFT JOIN
--ERP and Rep Resolve
(SELECT
MONTH(StatDate) AS Mth,
EmployeeID,
--SUM(ACSTopRepScore)/SUM(ACSEntireRepCount) AS ERP,
ACSTopRepScore AS TopRep,
ACSEntireRepCount AS RepCount,
--SUM(ACSRepResolvedScore)/SUM(ACSRepResolvedCount) AS RepResolve,
ACSRepResolvedScore AS RepResolve,
ACSRepResolvedCount AS RepResolveCount,
ACSNPSPromoter AS ACSNPSPromoter,
ACSNPSDetractor AS ACSNPSDetractor,
ACSNPSCount AS ACSNPSCount
FROM
dbReportSummary.ReportSummary.vwAfterCallSurvey WITH(NOLOCK)
WHERE
StatDate BETWEEN @StartDate AND @EndDate
GROUP BY
MONTH(StatDate),
EmployeeID,
ACSTopRepScore,
ACSEntireRepCount,
ACSRepResolvedScore,
ACSRepResolvedCount,
ACSNPSPromoter,
ACSNPSDetractor,
ACSNPSCount
) AS QA
ON
(QA.Mth IN (MONTH(DT.DateFull)))
AND QA.EmployeeID = E.EmployeeID
LEFT JOIN
--AHT--
(SELECT
MONTH(StatDate) AS Mth,
EmployeeId,
HandledCalls AS HandledCalls,
TalkTime AS TalkTime,
CallWorkTime AS CallWorkTime,
CASE
WHEN SUM(TalkTime) + SUM(CallWorkTime) = 0 OR SUM(HandledCalls) = 0
THEN 0.0
ELSE (SUM(TalkTime) + SUM(CallWorkTIme))/SUM(HandledCalls)
END 'AHT'
FROM
dbReportSummary.ReportSummary.vwACDAgentSummary WITH(NOLOCK)
WHERE
StatDate BETWEEN @StartDate AND @EndDate
GROUP BY
MONTH(StatDate),
EmployeeId,
HandledCalls,
TalkTime,
CallWorkTime
) AS AHT
ON
(AHT.Mth IN (MONTH(DT.DateFull)))
AND AHT.EmployeeID = E.EmployeeID
WHERE
(DT.DateFull <=@EndDate AND DT.DateFull >=@StartDate)
AND (E.StartDate <=@EndDate AND E.EndDate >=@StartDate)
AND (E.PeopleSoftDepartmentFunctionID = 'CSC')
AND E.PeopleSoftDepartmentID IN ('9030', '9080', '9355', '9040', '<<OSCCALTECH>>', '<<WDTSMO>>', '9195','<<OSVTS>>', '<<OSVTS_TCS>>','9085')
AND E.EmployeeID IS NOT NULL
AND (ES.PeopleSoftTitleStartDate BETWEEN DT.DateFull AND DT.DateFull + 90)
GROUP BY
DT.DateFull,
--DATEPART(MM,DT.DateFull),
--DATENAME(MM,DT.DateFull),
E.AreaID,
-- E.GeographicLocationID,
E.EmployeeID,
ES.PeopleSoftTitleStartDate,
QA.TopRep,
QA.RepCount,
QA.RepResolve,
QA.RepResolveCount,
QA.ACSNPSPromoter,
QA.ACSNPSDetractor,
QA.ACSNPSCount,
AHT.AHT,
AHT.TalkTime,
AHT.CallWorkTime,
AHT.HandledCalls
SELECT
D.DateFull,
--Date,
--[Month],
D.AreaID,
--GE.GeographicLocationDescription AS Center,
D.EmployeeID,
nE.EmployeeName,
D.PeopleSoftTitleStartDate,
D.TopRep,
D.RepCount,
D.ERP,
D.RepResolve,
D.RepResolveCount,
D.RR,
D.ACSNPSPromoter,
D.ACSNPSDetractor,
D.ACSNPSCount,
D.NPS,
D.AHT,
D.TalkTime,
D.CallWorkTime,
D.HandledCalls
FROM #DATA AS D
LEFT JOIN
dbEmployee.Config.vwName AS nE WITH (NOLOCK)
ON
D.EmployeeID = nE.EmployeeID
--LEFT JOIN
-- dbEmployee.Config.vwGeographicLocation AS GE WITH (NOLOCK)
--ON
-- GE.GeographicLocationID = D.GeographicLocationID
WHERE
(D.TopRep IS NOT NULL
AND D.RepCount IS NOT NULL
AND D.RepResolve IS NOT NULL
AND D.RepResolveCount IS NOT NULL
AND D.ACSNPSPromoter IS NOT NULL
AND D.ACSNPSDetractor IS NOT NULL
AND D.ACSNPSCount IS NOT NULL
AND D.AHT IS NOT NULL
AND D.TalkTime IS NOT NULL
AND D.CallWorkTime IS NOT NULL
AND D.HandledCalls IS NOT NULL
AND D.HandledCalls <> '0' )
GROUP BY
D.DateFull,
--Date,
--[Month],
D.AreaID,
--GE.GeographicLocationDescription,
D.EmployeeID,
nE.EmployeeName,
D.PeopleSoftTitleStartDate,
D.TopRep,
D.RepCount,
D.ERP,
D.RepResolve,
D.RepResolveCount,
D.RR,
D.ACSNPSPromoter,
D.ACSNPSDetractor,
D.ACSNPSCount,
D.NPS,
D.AHT,
D.TalkTime,
D.CallWorkTime,
D.HandledCalls
ORDER BY
D.DateFull ASC,
AreaID,
-- Center,
EmployeeName
DROP TABLE #DATA
/*Additional Info
Tech Depts - PeopleSoftDepartmentID IN ('9030', '9080', '9355', '9040', '<<OSCCALTECH>>', '<<WDTSMO>>', '9195','<<OSVTS>>', '<<OSVTS_TCS>>')
Bilingual Tech - PeopleSoftDepartmentID = '9085'
*/
output a row of performance results for each day
One row of output per day implies group by [Date]. Other columns in the group by clause will, well, cause the server to group by them, too, probably creating more than one row/day. My guess, though, glancing at your query, is that you're interested in one row per day for each employee (you said "rep"). In that case you'd want to group by employee, too.
If you want other non-aggregate columns in the results, join them in later. Here's a sketch:
select A.*, E.name, ... E.col_N
from (
select [Date], EmployeeID, max(foo) as x, sum(bar) as y, avg(zowie) as z
from tablename
group by [Date], EmployeeID
) as A
join Employees as E
on A.EmployeeID = E.EmployeeID