SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Recalculate Amount Column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nfpaccounting
Starting Member

USA
4 Posts

Posted - 09/05/2013 :  19:10:12  Show Profile  Reply with Quote
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
6062 Posts

Posted - 09/06/2013 :  17:03:14  Show Profile  Reply with Quote
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
Go to Top of Page

nfpaccounting
Starting Member

USA
4 Posts

Posted - 09/06/2013 :  20:00:51  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/06/2013 :  21:15:04  Show Profile  Reply with Quote
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
Go to Top of Page

nfpaccounting
Starting Member

USA
4 Posts

Posted - 09/08/2013 :  13:24:10  Show Profile  Reply with Quote
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
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/09/2013 :  12:03:16  Show Profile  Reply with Quote
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
Go to Top of Page

nfpaccounting
Starting Member

USA
4 Posts

Posted - 09/09/2013 :  19:24:37  Show Profile  Reply with Quote
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.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 09/10/2013 :  08:52:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000