Help with summing values

Transact-SQL
1

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.

Mike

2

This would do it, I think

SELECT e.employee
    ,e.lastname
    ,e.firstname
    ,d.perend
    ,CASE WHEN d.earnded > 0 
        THEN cast(d.earnded as varchar) 
        ELSE 'D' + cast(-d.earnded as varchar) 
    END AS Earning_deduction
    ,d.hours
    ,d.erate
FROM UPCHKD d
INNER JOIN UPEMPL e
    ON e.EMPLOYEE = d.EMPLOYEE
3

Hi,

Thanks for the quick response. I ran the query here but got an error that states:

Msg 8117, Level 16, State 1, Line 7
Operand data type char is invalid for minus operator.

Your help is greatly appreciated.

4 
select e.employee
      ,e.lastname
      ,e.firstname
      ,d.perend
      ,d.earnded
      ,d.hours
      ,d.erate
      ,d.hours*d.erate*case when substr(earned,1,1)='D' then -1 else 1 end as new_column
  from upchkd as d
       inner join upempl as e
               on e.employee=d.employee
5

The query runs good. Just had to spell out "substring" and add the alias d.earnded to it. I'm getting results now for a single employee, but it looks like I missed something as far as the math goes. We definitely don't want to multiply a deduction by zero, as that would not be good for the total amount. Can we make where if there is a deduction we don't multiply?

Thanks

6

Maybe this:

select e.employee
      ,e.lastname
      ,e.firstname
      ,d.perend
      ,d.earnded
      ,d.hours
      ,d.erate
      ,d.erate*case when substring(d.earned,1,1)='D' then -1 else d.hours end as new_column
  from upchkd as d
       inner join upempl as e
               on e.employee=d.employee
7

so "earnded" is not numeric?

8

corrected my previous post

9

EARNDED can be both. Some Examples:

0001
D012
0002
D015

A deduction will start with the letter "d"

I don't want to multiply the deductions by the hours because it will calculate as a zero.

10

Try the latest query I wrote for you