WHERE [StartedAt] > '2012-08-06 00:00:01.000'
AND [StartedAt] < '2012-08-010 23:59:59.000'
AND [ActivityId] = 10
ORDER BY [EMPLOYEEID]
This displays days that all employees have worked with a certain activity ID. Max 4 days Min 0 days. What I would be looking to do is remove any Employees from the table if they had worked all 4 days leaving only the Employees that have had a day or more off.
I might have approached this the wrong way but it is the only way I can think of getting the results I want.
I'm trying to run this as a Single Query in SQL Server Manager 2008.
Attachment shoes to Employees one with 4 entries who would be removed from the view the other with 3 who would remain the focus of the report.
with CTE as
WHERE [StartedAt] > '2012-08-06 00:00:01.000' AND
[StartedAt] < '2012-08-010 23:59:59.000' AND
[ActivityId] = 10
GROUP BY [EmployeeId]
HAVING COUNT(1) < 4
FROM CTE as c
JOIN [SR].[dbo].[TimeSheetConsolidated_All] as t
on t.[EmployeeId] = c.[EmployeeId]
WHERE t.[StartedAt] > '2012-08-06 00:00:01.000' AND
t.[StartedAt] < '2012-08-010 23:59:59.000' AND
t.[ActivityId] = 10
ORDER BY t.[EMPLOYEEID]