September 5, 2013 at 8:56 am
Recalculate "Amount" Column
--------------------------------------------------------------------------------
Our Salaried Employees have to log in their time by department and by type of Earnings (Regular, Holiday, Sick, etc). We are on a semi monthly pay period - therefore the number of hours worked each pay period vary - but the amount of pay is exactly the same each pay period.
On each employee's pay record there are 2 fields, Pay Per Period and Equivalent Hourly Rate.
When the payroll is calculated the, the payroll application calculates the Amount column based on a prorated hourly rate - so the total amount of payroll is exactly the same each pay period. (The prorated hourly rate may be different if the pay period has 80 hours vs. 104 hours - as the pay should be the same.
In the example below there are 5 employees each with a pay per period of $2000.
The business requirements are such that if an Employee has Vacation on their timecard - the Amount for the Vacation Hours should be calculated based on the "Equivalent Hourly Rate" and the balance on a prorated rate. The total of Vacation and Regular/Sick, etc. should equal the employee's pay for pay period - accounting also for any rounding differences. Below is a sample table - the amount column needs to be recalculated whenever there is VACATION used by Employee. For the Vacation Row the Amount should be the Hours times the Equivalent Hourly Rate and the other earnings items should be based on a prorated rate. I only need to Update the values of the Amount column.
For clarity sake I have added 2 additional columns - newrate and newamount. The newamount column values are the ones that I want to be replaced in the amount column. The newrate is a prorated rate that is based on number of hours worked less the vacation hours. For employees who do not have Vacation - those records should be ignored. Lastly, the round error should be resolved so the employee gets their gross wages in this example as 2000.
create TABLE Payroll
(
EmpID int,
EarningCode varchar(255),
PayPerPeriod varchar (255),
EQHourlyRate varchar(255),
HoursWorked varchar(255),
Amount varchar(255),
Dept varchar (255),
NewRate varchar (255),
NewAmount varchar (255)
);
Insert Into Payroll Values ( '100','Regular','2000','23.0769','52','1000','SALES','18.315','952.38');
Insert Into Payroll Values ( '100','Holiday','2000','23.0769','12','230.77','SALES','18.315','219.78');
Insert Into Payroll Values ( '100','Sick','2000','23.0769','12','230.77','SALES','18.315','219.78');
Insert Into Payroll Values ( '100','Jury Duty','2000','23.0769','8','153.85','SALES','18.315','146.52');
Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.31','SALES','23.0769','230.77');
Insert Into Payroll Values ( '100','Vacation','2000','23.0769','10','192.3','MKT','23.0769','230.77');
Insert Into Payroll Values ( '200','Regular','2000','23.0769','104','2000','ADMIN','n/a','2000');
Insert Into Payroll Values ( '300','Regular','2000','23.0769','88','1692.31','ACCT','n/a','1692.31');
Insert Into Payroll Values ( '300','Holiday','2000','23.0769','8','153.85','ACCT','n/a','153.85');
Insert Into Payroll Values ( '300','Sick','2000','23.0769','8','153.84','ACCT','n/a','153.84');
Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','HR','18.077','723.08');
Insert Into Payroll Values ( '400','Regular','2000','23.0769','40','769.23','ADMIN','18.076875','723.08');
Insert Into Payroll Values ( '400','Vacation','2000','23.0769','24','461.54','HR','23.0769','553.84');
Insert Into Payroll Values ( '500','Regular','2000','23.0769','66','1269.23','ACCT','18.0136708860759','1188.9');
Insert Into Payroll Values ( '500','Holiday','2000','23.0769','8','153.85','ACCT','18.0136708860759','144.11');
Insert Into Payroll Values ( '500','Vacation','2000','23.0769','5','96.15','ACCT','18.0136708860759','90.07');
Insert Into Payroll Values ( '500','Vacation','2000','23.0769','10','192.31','ADMIN','23.0769','230.77');
Insert Into Payroll Values ( '500','Vacation','2000','23.0769','15','288.46','HR','23.0769','346.15');
September 5, 2013 at 9:23 am
ChrisM@Work (9/5/2013)
Almost clear enough to make a stab at it...could you post the expected result set, or, alternatively, add an extra column say 'NewAmount' to your sample data which contains the values you are expecting to see in the 'Amount' column? Cheers.
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
September 5, 2013 at 10:43 am
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
September 6, 2013 at 1:58 am
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
September 6, 2013 at 3:16 pm
Chris,
Thank you so much - it works except for the rounding.
I tested it with different hours and in some cases the total salary for the employee ends up being 2000.01 or 2000.02 etc.
In my example, the total each employee was 2000 but the live database of course will have different amounts.
Essentially it is a valid assumption to make that the total of the Sum(Amount) Column for each employee where the Hoursworked are greater than 0 is their Gross Salary.
Any additional assistance will be highly appreciated.
Thanks
September 9, 2013 at 2:18 am
Try this - but beware, some of your original data is incorrect:
SELECT
EmpID, EarningCode, PayPerPeriod, EQHourlyRate, HoursWorked, Amount, Dept,
nRate = d.NewRate, nAmount = d.NewAmount,
NewRate = CAST(ROUND(sw.NewAmount/HoursWorked,4) AS DECIMAL(10,4)),
NewAmount = CAST(ROUND(ISNULL(sw.NewAmount,d.Amount),2,0) AS DECIMAL(10,4))
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
ORDER BY d.EmpID, d.EarningCode
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
September 9, 2013 at 11:38 am
Chris,
I did correct the data and ran the updated query as provided by you. I am still getting the rounding error. For example, I changed a few hours and rate to test it. Below is the result. The last column for EmpID 100 is 1000.01 and the last column for EmpID500 is 3000.01. Thanks
EmpIDEarningCodePayPerPeriodEQHourlyRateHoursWorkedAmountDeptnRatenAmountNewRateNewAmount
100Holiday100011.538520200SALES9.3406186.819.3406186.81
100Jury Duty100011.538510100SALES9.340693.419.340693.41
100Regular100011.538510100SALES9.340693.419.340693.41
100Sick100011.538530300SALES9.3406280.229.3406280.22
100Vacation100011.538510100SALES11.5385115.3911.5385115.39
100Vacation100011.538520200MKT11.5385230.7711.5385230.77
200Regular150017.3077181500ADMIN17.30771500NULL1500
300Holiday200023.076920645.16ACCT23.0769153.85NULL645.16
300Regular200023.076920645.16ACCT23.07691692.31NULL645.16
300Sick200023.076922709.68ACCT23.0769153.84NULL709.68
400Regular250028.8462731754.81HR23.99171751.423.99171751.4
400Regular250028.846230721.16ADMIN23.9917719.7523.9917719.75
400Vacation250028.8462124.04HR28.846228.8528.846228.85
500Regular300034.615410937.5ADMIN34.6154937.5192.30761923.08
500Sick300034.61542187.5ACCT18.0137187.5192.3076384.62
500Vacation300034.6154201875HR34.6154187534.6154692.31
September 10, 2013 at 1:11 am
I'm sure we can work it out if you can knock up a suitable sample data set. The problem you are facing is that the data you have is already rounded - any algorithm replacing or supplementing the existing one must match its rounding methods.
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
September 10, 2013 at 6:46 pm
Perhaps it is overkill but is this a case where "fudge rounding" would help?
http://www.sqlservercentral.com/articles/Financial+Rounding/88067/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 11, 2013 at 1:03 am
dwain.c (9/10/2013)
Perhaps it is overkill but is this a case where "fudge rounding" would help?http://www.sqlservercentral.com/articles/Financial+Rounding/88067/
Dwain - possibly. The OP is using the rounded result of an earlier calculation to perform subsequent calculations. Without knowing what rounding technique was used in the earlier calculations, balancing the subsequent figures is going to be difficult.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply