How Do I Filter a Group of Rows based on one Column's Value in TSQL? 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 &gt; 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.
Do you really need the GROUP BY, are those rows not distinct?

You can add a conditional CASE to find rows with 'Overtime Pay'

WITH RESULTS AS
(
SELECT 
      [FullName]
  ,[PayHours]
  ,[SSN]
  ,[EffectiveDate]
  ,[PayDescription]
  ,[OrderNumber]
  ,[WeekStart]
  ,[WeekEnd]
  ,SUM(PayHours)
   OVER(Partition BY FullName,WeekStart) AS TotalHours
  ,MAX(CASE WHEN PayDescription ='Overtime Pay' THEN 1 ELSE 0 END) 
   OVER(Partition BY FullName,WeekStart) AS OvertimePayFlag


  FROM [viewReportPotentialOverTime]

  WHERE EffectiveDate BETWEEN '01/01/2015 00:00:00' AND '10/23/2015 23:59:59' 

   -- really needed?
  Group By FullName,SSN,WeekStart, WeekEnd, EffectiveDate, OrderNumber, PayDescription, PayHours
  )
SELECT * FROM RESULTS
WHERE TotalHours &gt; 40  -- no HAVING needed
  AND OvertimePayFlag = 1
Order By FullName, WeekStart, EffectiveDate