asked on

Having sum greater than not working

I have the following query where I am trying to employee id from payroll records where the employee that worked over time excluding sick, vacation, holiday and Jury time. This query is returning a few records where the hours is equal to 40.

SELECT E.EMPLOYEE_ID FROM Payroll.PAYROLLRECORDS PR
                                                INNER JOIN Payroll.EMPLOYEE E ON E.ID = PR.EMPLOYEEID
                                                WHERE E.PAYROLL_TYPE IN ('W')
                  AND PR.PAYROLLPOSTID = 14
                  GROUP BY E.EMPLOYEE_ID
                  HAVING (SUM(ISNULL(PR.REGULAR_HOURS,0)) - SUM(ISNULL(PR.SICK_HOURS,0)) - SUM(ISNULL(PR.VACATION_HOURS,0)) - SUM(ISNULL(PR.HOLIDAY_HOURS,0)) - SUM(ISNULL(PR.RT_HOURS,0))) > 40
                  ORDER BY E.EMPLOYEE_ID

Mike Eghtebas

What do you intend to get from it?

Mike Eghtebas

You probably need to include these predicates in WHERE clause not having clause.

SELECT E.EMPLOYEE_ID 
FROM Payroll.PAYROLLRECORDS PR 
INNER JOIN Payroll.EMPLOYEE E ON E.ID = PR.EMPLOYEEID
WHERE E.PAYROLL_TYPE IN ('W') AND PR.PAYROLLPOSTID = 14 AND 
(ISNULL(PR.REGULAR_HOURS,0) - ISNULL(PR.SICK_HOURS,0) - ISNULL(PR.VACATION_HOURS,0) - ISNULL(PR.HOLIDAY_HOURS,0) - ISNULL(PR.RT_HOURS,0)) > 40
GROUP BY E.EMPLOYEE_ID
ORDER BY E.EMPLOYEE_ID

Open in new window

As you have, it will show those employees their group by total is over 40

This post has been revised

ASKER CERTIFIED SOLUTION

Snarf0001

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

axnst2

ASKER

Snarf0001 - You're assumption is correct about the data. This is daily hours worked that must be summed up for the week. In my testing I did have the aggregrate in my select and that is how I saw records with hours of 40. I thought this concept could have been achieved without a sub query?

I tried with same data types.
SELECT EMPLOYEE_ID
            ,(SUM(ISNULL(PR.REGULAR_HOURS,0.0)) - SUM(ISNULL(PR.SICK_HOURS,0.0)) - SUM(ISNULL(PR.VACATION_HOURS,0.0)) - SUM(ISNULL(PR.HOLIDAY_HOURS,0.0)) - SUM(ISNULL(PR.RT_HOURS,0.0))) as hrs
FROM Payroll.PAYROLLRECORDS PR
                                                INNER JOIN Payroll.EMPLOYEE E ON E.ID = PR.EMPLOYEEID
                                                WHERE E.PAYROLL_TYPE IN ('W')
                  AND PR.PAYROLLPOSTID = 14
                  GROUP BY E.EMPLOYEE_ID
                  HAVING (SUM(ISNULL(PR.REGULAR_HOURS,0.0)) - SUM(ISNULL(PR.SICK_HOURS,0.0)) - SUM(ISNULL(PR.VACATION_HOURS,0.0)) - SUM(ISNULL(PR.HOLIDAY_HOURS,0.0)) - SUM(ISNULL(PR.RT_HOURS,0.0))) > 40.0

Here is a sample of the results.
Employee_ID Hrs
130382        40.34
278644        45.74
262794        40
274897        40
252146        42.49

Snarf0001

Are all the hour columns the same data type (REGULAR_HOURS, SICK_HOURS, VACATION_HOURS, HOLIDAY_HOURS, RT_HOURS)?
And what type would that be, both the type and the precision?

Can you take one of the problem employees (262794) and post all the non-aggregated records from PAYROLLRECORDS?
Would like to see the raw data.

axnst2

ASKER

It was the data types. This is what I had to end up doing to get it too work. Seems ugly but works!

Thanks.

SELECT EMPLOYEE_ID
            ,(SUM(ISNULL(CAST (PR.REGULAR_HOURS AS NUMERIC(4,2)),0.00)) - SUM(ISNULL(CAST(PR.SICK_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.VACATION_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.HOLIDAY_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.RT_HOURS AS numeric(4,2)),0.00))) as hrs
FROM Payroll.PAYROLLRECORDS PR
                                                INNER JOIN Payroll.EMPLOYEE E ON E.ID = PR.EMPLOYEEID
                                                WHERE E.PAYROLL_TYPE IN ('W')
                  AND PR.PAYROLLPOSTID = 14
                  GROUP BY E.EMPLOYEE_ID
                  HAVING (SUM(ISNULL(CAST (PR.REGULAR_HOURS AS NUMERIC(4,2)),0.00)) - SUM(ISNULL(CAST(PR.SICK_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.VACATION_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.HOLIDAY_HOURS AS numeric(4,2)),0.00)) - SUM(ISNULL(CAST(PR.RT_HOURS AS numeric(4,2)),0.00))) > 40.00