Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    8

    Unanswered: Remove All Instances with 4 Entries

    SELECT [TimeSheetConsolidatedId]
    ,[EmployeeId]
    ,[ActivityId]
    ,[StartedAt]
    ,[HoursWorked]
    FROM [SR].[dbo].[TimeSheetConsolidated_All]
    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.

    Thanks
    Attached Thumbnails Attached Thumbnails Untitled.jpg  

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try:

    Code:
    with CTE as
    (
        SELECT 
            [EmployeeId]
        FROM [SR].[dbo].[TimeSheetConsolidated_All]
        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
    )
    
    SELECT 
        t.[TimeSheetConsolidatedId],
        t.[EmployeeId],
        t.[ActivityId],
        t.[StartedAt],
        t.[HoursWorked]
    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]
    Hope this helps.

  3. #3
    Join Date
    Aug 2012
    Posts
    8

    Huge Thank You!

    Awesome top half works how I would like it to. Can I ask what this part is doing though?

    FROM CTE as c?

    Thanks

  4. #4
    Join Date
    Apr 2012
    Posts
    213

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •