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
axnst2Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Mike EghtebasDatabase and Application DeveloperCommented:
What do you intend to get from it?
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
0
 
Snarf0001Commented:
I think that's what he wants.  Both just guessing, but I'm assuming that PayrollRecords has daily info for each employee of what hours and what types they worked, and you're trying to aggregate on the week.
In which case the having clause is where they need to be.

But the query you have looks perfectly valid.  Is there any possibility of it being rounded due to different data types?

Can you just add the aggregate to the select and see what it's bringing back?

SELECT E.EMPLOYEE_ID, (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))) as ReturnedHours
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 

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
axnst2Author Commented:
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
0
 
Snarf0001Commented:
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.
0
 
axnst2Author Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.