March 12, 2015 at 10:27 am
How do I filter a list of Employees where the Sum of "VALIDATED" hours is less than 80? For example.
Here is the flat table
SELECT EMP_NO, hours, IsValidated, rate_type
FROM Pay_Records
WHERE pay_period_id = 2
Order by EMP_NO
Output will be something like this
12345 | 2 |true |REG
12345 | 15 |false |OVR
12345 | 30 |true |OVER
33334 |2| true |REG
Total Validated hours for the Employee 12345 will be 32 NOT 47. How do I list employees who worked less than 80 validated hours. The hours are validated only when it is true.
PLease advise
Thanks!
March 12, 2015 at 10:36 am
I added data to your sample to make it easier to show the filtering
Basically, we get what you want with a CASE inside the SUM
This gets each employee and their validated hours
WITH Pay_Records (EMP_NO, [hours], IsValidated, rate_type) AS (
SELECT 12345,2,'true','REG'
UNION ALL
SELECT 12345,50,'false','OVR'
UNION ALL
SELECT 12345,30,'true','OVER'
UNION ALL
SELECT 33334,2,'true','REG'
UNION ALL
SELECT 33334,40,'true','REG'
UNION ALL
SELECT 33334,41,'true','REG'
)
SELECT EMP_NO, SUM(CASE WHEN IsValidated='true' THEN [hours] ELSE 0 END) ValidatedHours
FROM Pay_Records
GROUP BY EMP_NO
Then to filter out the ones at or above 80 hours we add
HAVING SUM(CASE WHEN IsValidated='true' THEN [hours] ELSE 0 END) < 80
If you need more details about the employee on the same line, we might need the windowing SUM instead. LMK and we can do that too
March 12, 2015 at 11:09 am
Excellent! Thank you so much!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply