mardi 20 janvier 2015

running total for each employee

I have the following query which works great to return a running total of carpool reimbursements for an individual staff member. (employee earns $30 for each 20 trips, trips roll over until the end of the year).



-- carpool quarter stats
use TRPTracking

declare @employeeID NVarChar(100), @year Char(4)
Set @employeeID = 'PSmith'
Set @year = '2014'
------
declare @startDate DateTime, @endDate DateTime
Set @startDate = '1/1/' + @year
Set @endDate = DateAdd(d,-1,DateAdd(yyyy,1,@startDate))

DECLARE @calendar TABLE (Date datetime)

WHILE (@startDate <= @endDate) BEGIN
INSERT INTO @Calendar VALUES (@startDate)
SET @startDate = DATEADD(quarter, 1, @startDate)
END

DECLARE @CarpoolTbl TABLE (quarter varchar(250), value decimal(18,1), runningTotal decimal(18,1), earned money)
DECLARE @runningTotal decimal(18,1), @earned money
SET @runningTotal = 0
SET @earned = 0

INSERT INTO @CarpoolTbl
SELECT CASE DatePart(q, c.date)
WHEN 1 THEN 'Jan-Mar'
WHEN 2 THEN 'Apr-Jun'
WHEN 3 THEN 'Jul-Sep'
WHEN 4 THEN 'Oct-Dec' END AS quarter,
IsNULL(Sum(t.value),0) AS value,
null,
0
FROM @calendar c
LEFT OUTER JOIN events e ON (DatePart(q, c.date) = DatePart(q, e.eventDate) AND e.employeeID = @employeeID AND e.eventType = 'CP' AND Year(eventDate) = @year)
LEFT JOIN types t ON t.typeID = e.eventType
GROUP BY DatePart(q, c.date)

UPDATE @CarpoolTbl
SET @earned = earned = Floor((@runningTotal + value)/20) - Floor(@runningTotal/20),
@runningTotal = runningTotal = @runningTotal + value

FROM @CarpoolTbl

SELECT quarter, value, runningTotal, earned * 30 AS earned
FROM @CarpoolTbl


Now, I want a query that returns this information for all employees. I remove the portion that relates to employeeID and I get what looks to be good. But... what is happening is my running total is running for everyone. I need it to restart for each employee. I can't quite figure out where to add the employeeID grouping in the running total.



-- carpool quarter stats
use TRPTracking

declare @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))

DECLARE @calendar TABLE (Date datetime)

WHILE (@startDate <= @endDate) BEGIN
INSERT INTO @Calendar VALUES (@startDate)
SET @startDate = DATEADD(quarter, 1, @startDate)
END

DECLARE @CarpoolTbl TABLE (dateQ int, quarter varchar(250), employeeID varchar(255), value decimal(18,1), runningTotal decimal(18,1), earned money)
DECLARE @runningTotal decimal(18,1), @earned money
SET @runningTotal = 0
SET @earned = 0

INSERT INTO @CarpoolTbl
SELECT
DatePart(q, c.date),
CASE DatePart(q, c.date)
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,
IsNULL(Sum(t.value),0) AS value,
null,
0
FROM @calendar c
LEFT OUTER JOIN events e ON (DatePart(q, c.date) = DatePart(q, e.eventDate)
AND e.eventType = 'CP' AND Year(eventDate) = @year)
LEFT JOIN types t ON t.typeID = e.eventType
GROUP BY e.employeeID, DatePart(q, c.date)

UPDATE @CarpoolTbl
SET @earned = earned = Floor((@runningTotal + value)/20) - Floor(@runningTotal/20),
@runningTotal = runningTotal = @runningTotal + value

FROM @CarpoolTbl

SELECT c.quarter, c.employeeID, a.DisplayName AS employee, c.value AS trips, earned * 30 AS earned
FROM @CarpoolTbl c
LEFT JOIN SBAIntranet.dbo.NTAuth a ON 'SBA\' + c.employeeID = a.AccountName
ORDER BY dateQ, employeeID


Any thoughts?


Aucun commentaire:

Enregistrer un commentaire