nfpacct (9/5/2013)
Chris,I added 2 new columns - NewAmount and NewRate (The newamount is based on the calculated newrate)
The desired result is the existing Amount Column values to be replaced with NewAmount column.
Thanks
Cheers. Here you go:
-- Correction to your datatypes
SELECT
EmpID,
EarningCode,
PayPerPeriod= CAST(PayPerPeriod AS INT),
EQHourlyRate= CAST(EQHourlyRate AS DECIMAL (10,4)),
HoursWorked= CAST(HoursWorked AS INT),
Amount= CAST(Amount AS DECIMAL (10,2)),
Dept,
NewRate= CAST(NULLIF(NewRate,'n/a') AS DECIMAL (10,4)),
NewAmount= CAST(NewAmount AS DECIMAL (10,4))
INTO #Payroll
FROM Payroll
-- Solution
SELECT
EmpID, EarningCode, PayPerPeriod, EQHourlyRate, HoursWorked, Amount, Dept,
--nRate = d.NewRate, nAmount = d.NewAmount,
NewRate = sw.NewAmount/HoursWorked,
NewAmount = ISNULL(sw.NewAmount,d.Amount)
FROM (
SELECT EmpID, EarningCode, PayPerPeriod, EQHourlyRate, HoursWorked, Amount, Dept,
--p.NewRate, p.NewAmount,
RemainingAmount = p.PayPerPeriod - v.VacationTotal,
RemainingHoursWorked = SUM(p.HoursWorked) OVER(PARTITION BY p.EmpID) - v.VacationHours
FROM #Payroll p
CROSS APPLY (
SELECT
VacationTotal = SUM(HoursWorked*EQHourlyRate),
VacationHours = SUM(HoursWorked)
FROM #Payroll ip
WHERE ip.EmpID = p.EmpID
AND ip.EarningCode = 'Vacation'
) v
) d
CROSS APPLY (
SELECT NewAmount = CASE
WHEN d.EarningCode = 'Vacation' THEN EQHourlyRate*HoursWorked
ELSE (HoursWorked*RemainingAmount)/RemainingHoursWorked END
) sw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden