Author 
Topic 

nfpaccounting
Starting Member
USA
4 Posts 
Posted  09/05/2013 : 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
Flowing Fount of Yak Knowledge
USA
5932 Posts 
Posted  09/06/2013 : 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
USA
4 Posts 
Posted  09/06/2013 : 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
Flowing Fount of Yak Knowledge
USA
5932 Posts 
Posted  09/06/2013 : 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 nonvacation hours. 3. divide that by the total nonvacation hours to get the prorated rate (newRate). 4. multiply that [newRate] * each HoursWorked for nonvacation earningCode row.
Be One with the Optimizer TG 


nfpaccounting
Starting Member
USA
4 Posts 
Posted  09/08/2013 : 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
Flowing Fount of Yak Knowledge
USA
5932 Posts 
Posted  09/09/2013 : 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
USA
4 Posts 
Posted  09/09/2013 : 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
Flowing Fount of Yak Knowledge
USA
5932 Posts 
Posted  09/10/2013 : 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 



Topic 


