This is a continuation of my earlier question. After some assistance, I was able to correctly return a running total of carpool trips for each employee. Now, I need to add how money the employee earns for the quarter. For each 20 trips, they earn $30, with anything over mod of 20 rolling over to the next quarter. My old method updated the value in a temp table. Here is my current query:
declare @employeeID NVarChar(100), @year Char(4)
Set @year = '2014'
------
declare @startDate DateTime, @endDate DateTime
Set @startDate = '1/1/' + @year
Set @endDate = DateAdd(d,-1,DateAdd(yyyy,1,@startDate))
;WITH cte
AS (SELECT Datepart(qq, e.eventDate) AS quarterNum,
CASE DatePart(qq, e.eventDate)
WHEN 1 THEN 'Jan-Mar'
WHEN 2 THEN 'Apr-Jun'
WHEN 3 THEN 'Jul-Sep'
WHEN 4 THEN 'Oct-Dec' END AS quarter,
e.employeeID,
Sum(t.value) AS trips
FROM events e
LEFT JOIN types t ON t.typeID = e.eventType
WHERE e.eventType = 'CP' AND Year(eventDate) = @year
GROUP BY Datepart(quarter, e.eventDate), e.employeeID)
SELECT a.quarter, a.employeeID, nta.DisplayName AS employee, trips,
(SELECT Sum(trips)
FROM cte b
WHERE a.employeeID = b.employeeID
AND a.quarterNum >= b.quarterNum) AS runningTripsTotal,
0 AS runningEarned
FROM cte a
LEFT JOIN SBAIntranet.dbo.NTAuth nta ON 'SBA\' + a.employeeID = nta.AccountName
ORDER BY a.employeeID, a.quarterNum
Aucun commentaire:
Enregistrer un commentaire