Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Recalculate Wages (Reposted for SQL 2008 with additional columns) Expand / Collapse
Author
Message
Posted Thursday, September 5, 2013 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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');
Post #1491832
Posted Thursday, September 5, 2013 9:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1491858
Posted Thursday, September 5, 2013 10:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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
Post #1491885
Posted Friday, September 6, 2013 1:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1492074
Posted Friday, September 6, 2013 3:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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


Post #1492433
Posted Monday, September 9, 2013 2:18 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1492686
Posted Monday, September 9, 2013 11:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 18, 2014 3:01 PM
Points: 38, Visits: 176
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

EmpID EarningCode PayPerPeriod EQHourlyRate HoursWorked Amount Dept nRate nAmount NewRate NewAmount
100 Holiday 1000 11.5385 20 200 SALES 9.3406 186.81 9.3406 186.81
100 Jury Duty 1000 11.5385 10 100 SALES 9.3406 93.41 9.3406 93.41
100 Regular 1000 11.5385 10 100 SALES 9.3406 93.41 9.3406 93.41
100 Sick 1000 11.5385 30 300 SALES 9.3406 280.22 9.3406 280.22
100 Vacation 1000 11.5385 10 100 SALES 11.5385 115.39 11.5385 115.39
100 Vacation 1000 11.5385 20 200 MKT 11.5385 230.77 11.5385 230.77
200 Regular 1500 17.3077 18 1500 ADMIN 17.3077 1500 NULL 1500
300 Holiday 2000 23.0769 20 645.16 ACCT 23.0769 153.85 NULL 645.16
300 Regular 2000 23.0769 20 645.16 ACCT 23.0769 1692.31 NULL 645.16
300 Sick 2000 23.0769 22 709.68 ACCT 23.0769 153.84 NULL 709.68
400 Regular 2500 28.8462 73 1754.81 HR 23.9917 1751.4 23.9917 1751.4
400 Regular 2500 28.8462 30 721.16 ADMIN 23.9917 719.75 23.9917 719.75
400 Vacation 2500 28.8462 1 24.04 HR 28.8462 28.85 28.8462 28.85
500 Regular 3000 34.6154 10 937.5 ADMIN 34.6154 937.5 192.3076 1923.08
500 Sick 3000 34.6154 2 187.5 ACCT 18.0137 187.5 192.3076 384.62
500 Vacation 3000 34.6154 20 1875 HR 34.6154 1875 34.6154 692.31
Post #1492884
Posted Tuesday, September 10, 2013 1:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1493014
Posted Tuesday, September 10, 2013 6:46 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 10:55 PM
Points: 3,648, Visits: 5,321
Perhaps it is overkill but is this a case where "fudge rounding" would help?

http://www.sqlservercentral.com/articles/Financial+Rounding/88067/



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1493411
Posted Wednesday, September 11, 2013 1:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 7,284, Visits: 13,824
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1493471
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse