I am trying to make a SQL query in Server 2008 R2 that selects the rows only for employees who have over 40 hours in a pay week and a pay description that doesn't include "over time". So far I have the following:
WITH RESULTS AS
(
SELECT
[FullName]
,[PayHours]
,[SSN]
,[EffectiveDate]
,[PayDescription]
,[OrderNumber]
,[WeekStart]
,[WeekEnd]
,SUM(PayHours)OVER(Partition BY FullName,WeekStart) AS TotalHours
FROM [viewReportPotentialOverTime]
WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59'
Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours
)
SELECT * FROM RESULTS
Having TotalHours > 40 --AND PayDescription !='Overtime Pay'
Order By FullName, WeekStart, EffectiveDate
This gets me close to what I need. The problem I'm having is filtering out the rows for employees that do include a PayDesciption of 'Overtime Pay'. When I uncomment the !='Overtime Pay' then it filters out just the one row for that pay week that was 'Overtime Pay' and leaves all of the Regular Pay rows. However, what I need is for it to filter out all rows for that pay week (which I used the WeekStart column to determine the pay weeks)for that employee. I'm using this as an audit to make sure whenever someone worked over 40 hours in a week that they were paid OT.
I've also tried a similar version using the HAVING clause, but the problem I run into there is that you can't have a windowed function in a HAVING clause.
Best Answer
Do you really need the
GROUP BY
, are those rows not distinct?You can add a conditional
CASE
to find rows with 'Overtime Pay'