Hello, I need some help with a query that I'm trying to create for a check stub report. So far I have two tables:
UPEMPL (Emp Information)
UPCHKD (Check Detail)
The query looks like this
SELECT e.employee, e.lastname, e.firstname, d.perend, d.earnded, d.hours, d.erate
from UPCHKD d
Join UPEMPL e on e.EMPLOYEE = d.EMPLOYEE
This will pull in all employees checks. There are 3 columns I am concerned with:
d.earnded, d.erate, d.hours
These are the earnings/deductions and how many hours there are for each one.
I would like to sum up the earnings and put them into a column with the total. However, the trick is determining if the value is an earning or a deduction.
An earning would be a plus and a deduction would be a minus
For example, if I query one person for one payperiod, their records look like this:
employee lastname firstname perend earnded hours erate
10 Smith John 07052015 0001 96.00 2.64423
10 Smith John 07052015 0002 34.00 3.30529
10 Smith John 07052015 0007 8.00 2.64423
10 Smith John 07052015 0213 0.00 0.00000
10 Smith John 07052015 D011 0.00 175.00000
An earning will start with a number and a deduction will start with the character "D"
So I'd like to create a column that will show how much they made (or lost) by each earning or deduction. So maybe take the hours x's the erate and put it into a column.
Then, is there a way to give a grand total with summation of all earning and subtraction of the deductions?
Thanks as always you guys are the best.