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

    Unanswered: No Error but Results not accurate

    SELECT
    ts.[EmployeeId]
    ,p.[FirstName]
    ,p.[LastName]
    ,p.[KnownAs]
    ,ts.[StartedAt]
    ,ts.[HoursWorked]
    FROM [SR].[dbo].[TimeSheetConsolidated_All] as ts
    JOIN [SR].[dbo].[Persons] as p
    ON p.PersonId=ts.EmployeeId
    WHERE [StartedAt] > '2012-08-13 00:00:01.000' AND
    [StartedAt] < '2012-08-17 23:59:59.000' AND
    [ActivityId] = 10
    GROUP BY ts.[EmployeeId], p.[FirstName] ,p.[LastName],p.[KnownAs],ts.[StartedAt],ts.[HoursWorked]
    HAVING COUNT(1) < 4;

    This query should show people that have worked under 4 days but it displays the full database including all the people with 4 or more entries.

    It works if the [StartedAt] and [HoursWorked] Fields are not included but I need these fields included.

    I don't know where I'm going wrong!!

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    SELECT
       ts.[EmployeeId], p.[FirstName], p.[LastName]
    ,  p.[KnownAs], ts.[StartedAt], ts.[HoursWorked]
       FROM [SR].[dbo].[TimeSheetConsolidated_All] as ts
       JOIN [SR].[dbo].[Persons] as p
          ON p.PersonId=ts.EmployeeId
       WHERE  '2012-08-13 00:00:01.000' < ts.[StartedAt]
          AND ts.[StartedAt] < '2012-08-17 23:59:59.000' 
          AND [ActivityId] = 10
          AND 4 < (SELECT Count(*)
             FROM [SR].[dbo].[TimeSheetConsolidated_All] AS z
             WHERE z.[employeeId] = ts.[employeeId]
                AND '2012-08-13 00:00:01.000' < z.[StartedAt]
                AND z.[StartedAt] < '2012-08-17 23:59:59.000'
                AND 10 = [ActivityId]
             GROUP BY DATEADD(day, 0, DateDiff(day, 0, z.[StartedAt])))
    Is this better, worse, or other?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Aug 2012
    Posts
    8
    Thanks Not too sure.

    Msg 512, Level 16, State 1, Line 2
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Any other tweaks I could make to make it run?

Posting Permissions

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