Recalculate Amount Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Recalculate Amount Column

Author

Topic

nfpaccounting
Starting Member

4 Posts

Posted - 2013-09-05 : 19:10:12

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');

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-06 : 17:03:14

This is pretty close. I changed your numeric columns to appropriate datatypes. and your 'N/A' I switched with NULL. Also, I think one of your [newAmount] values is wrong in your sample: the row with EmpID=500, Earningcode=Vacation, Hoursworked=5.


create TABLE #Payroll
(
EmpID int,
EarningCode varchar(15),
PayPerPeriod money,
EQHourlyRate money,
HoursWorked smallint,
Amount money,
Dept varchar (15),
NewRate money,
NewAmount money
);
set nocount on
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',null,'2000');
Insert Into #Payroll Values ( '300','Regular','2000','23.0769','88','1692.31','ACCT',null,'1692.31');
Insert Into #Payroll Values ( '300','Holiday','2000','23.0769','8','153.85','ACCT',null,'153.85');
Insert Into #Payroll Values ( '300','Sick','2000','23.0769','8','153.84','ACCT',null,'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');

;with a (EmpID, PayPerPeriod, EQHourlyRate, totOthHrs, totVacHrs) 
as 
(
       select EmpID
              , min(PayPerPeriod) PayPerPeriod
              , min(EQHourlyRate) EQHourlyRate
              , sum(case when EarningCode = 'Vacation' then 0 else hoursworked end) 
              , sum(case when EarningCode = 'Vacation' then hoursworked else 0 end)
       from #payroll 
       group by EmpID
) 
, newRate (EmpID, newRate, totVacHrs) 
as (select EmpID, (PayPerPeriod - (totVacHrs * EQHourlyRate)) / totOthHrs, totVacHrs from a)

select p.EmpID
       ,p.EarningCode
       ,p.PayPerPeriod
       ,p.EQHourlyRate
       ,p.HoursWorked
       ,p.newAmount
       ,convert(decimal(12,2),
              case 
              when totVacHrs = 0 then Amount
              when EarningCode = 'Vacation' then p.EQHourlyRate * HoursWorked 
              else (nr.newRate * HoursWorked) 
              end) as newAmount2
       ,p.Dept
from   newRate as nr
join   #payroll as p
       on p.EmpID = nr.EmpID

OUTPUT:
EmpID       EarningCode     PayPerPeriod          EQHourlyRate          HoursWorked newAmount             newAmount2   Dept
----------- --------------- --------------------- --------------------- ----------- --------------------- ----------------------------
100         Regular         2000.00               23.0769               52          952.38                952.38       SALES
100         Holiday         2000.00               23.0769               12          219.78                219.78       SALES
100         Sick            2000.00               23.0769               12          219.78                219.78       SALES
100         Jury Duty       2000.00               23.0769               8           146.52                146.52       SALES
100         Vacation        2000.00               23.0769               10          230.77                230.77       SALES
100         Vacation        2000.00               23.0769               10          230.77                230.77       MKT
200         Regular         2000.00               23.0769               104         2000.00               2000.00      ADMIN
300         Regular         2000.00               23.0769               88          1692.31               1692.31      ACCT
300         Holiday         2000.00               23.0769               8           153.85                153.85       ACCT
300         Sick            2000.00               23.0769               8           153.84                153.84       ACCT
400         Regular         2000.00               23.0769               40          723.08                723.08       HR
400         Regular         2000.00               23.0769               40          723.08                723.08       ADMIN
400         Vacation        2000.00               23.0769               24          553.84                553.85       HR
500         Regular         2000.00               23.0769               66          1188.90               1166.32      ACCT
500         Holiday         2000.00               23.0769               8           144.11                141.37       ACCT
500         Vacation        2000.00               23.0769               5           90.07                 115.38       ACCT
500         Vacation        2000.00               23.0769               10          230.77                230.77       ADMIN
500         Vacation        2000.00               23.0769               15          346.15                346.15       HR

Be One with the Optimizer
TG

nfpaccounting
Starting Member

4 Posts

Posted - 2013-09-06 : 20:00:51

TJ - Thanks for your feedback.
Unfortunately it is not working as desired.

For example, I changed the hours to 13 hours the NewAmount did not populate correctly. SInce these are Salaried Employees, their Salary will vary and the number of Hours will vary. The key requirement here is that a/ Their Vacation should be calculated based on the EQHourlyRate. The balance of earnings should be calculated by Total Salary minus Vacation Amount. Then divided by the number of hours (except vacation) - the number of hours can vary as some payperiods a person might only work 80 hours - but they will still get their full pay.

If a person works 20 hours Regular, 20 Hours Holiday and 40 Hours Vacation and their regular pay is $2000 - then vacation pay will be 23.07698 * 40 hours.
The balance 20 Hours Regular will be calculated as
(Total Pay Per Period - Total Vacation Pay)/40 * 20

Hope this helps.

Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-06 : 21:15:04

you mean the NewAmount2 did not populate correctly, right?
add more rows to the sample to illustrate the issue with "13". As you can see I added a [newAmount2] next to your [newAmount] column and they match for all the other rows for the most part. So more sample data will help with the expected results.

Basically the logic I used was:
1. get the vacation pay (total vacation hours * EQHourlyRate)
2. subtract that from PayPerPeriod to get the total pay for non-vacation hours.
3. divide that by the total non-vacation hours to get the pro-rated rate (newRate).
4. multiply that [newRate] * each HoursWorked for non-vacation earningCode row.

Be One with the Optimizer
TG

nfpaccounting
Starting Member

4 Posts

Posted - 2013-09-08 : 13:24:10

TG - Yes you are correct it is working quite well.
Can you assist with 2 more items with the query.

1/ The rounding error is still an issue. When the total of the newamount2 is added it is off by 1 or 2 pennies.
Any rounding error should be posted to Vacation.
2/ The newamount columns were there for illustration purposes. What I need is an Update Query that will update the Amount Column with calculated.

3/ The query should only be run for employees who have vacation and nonvacation earnings code. I was able to figure this out by placing the following in the Newrate calculation

where totVacHrs>0 and totOthHrs >0)
Because if an employee earns all Vacation then there is no allocation and if the employee has no vacation then there is no allocation.

Thank you again for your assistance

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-09 : 12:03:16

To change the statement to an update is simple. Just change this:


select p.EmpID
       ,p.EarningCode
       ,p.PayPerPeriod
       ,p.EQHourlyRate
       ,p.HoursWorked
       ,p.newAmount
       ,convert(decimal(12,2),
              case 
              when totVacHrs = 0 then Amount
              when EarningCode = 'Vacation' then p.EQHourlyRate * HoursWorked 
              else (nr.newRate * HoursWorked) 
              end) as newAmount2
       ,p.Dept
from   newRate as nr
join   #payroll as p
       on p.EmpID = nr.EmpID

to this:


update p set
       Amount = convert(decimal(12,2),
              case 
              when totVacHrs = 0 then Amount
              when EarningCode = 'Vacation' then p.EQHourlyRate * HoursWorked 
              else (nr.newRate * HoursWorked) 
              end)
from   newRate as nr
join   #payroll as p
       on p.EmpID = nr.EmpID
where  nr.totVacHrs > 0
and    nr.newRate != p.EQHourlyRate

The rounding is a little tricky as you can't know if the sum of the calculated amounts is different from the PayPerPeriod until they are all applied. So The simplest thing may be to take another pass and update the amount for vacation rows accordingly when that happens.

Let me know if you need help with that. If you do I need a way to uniquely identify a specific row. For instance how can I isolate a single row if these values are all the same between 2 rows (EmpID, EarningCode, HoursWorked)?

Be One with the Optimizer
TG

nfpaccounting
Starting Member

4 Posts

Posted - 2013-09-09 : 19:24:37

TG - thank you so much for your assistance.
Yes, it will be great if you can add the rounding logic to the query.

Again thanks for your time on this.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-09-10 : 08:52:45

Then I need to know the answer to my question: how to uniquely identify a specific row? What is the primary key of the table? Is there a unique constraint?

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources