Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Query Returns Inaccurate Count

    Why does this return 64.00000 Hours Worked when if you do the calculation yourself you see that it should only be 16? And if there is a better way to write this query please let me know I am open to suggestions.
    Code:
    Create Table #One
    (
      BadgeNum int,
      NameOnFile varchar(1000),
      hoursworked int
    )
     
    Create Table #Two
    (
      ID int,
      arrest_status varchar(1000),
      supervisorSignoff varchar(500),
      officerName varchar(1000)   
    )
     
    INSERT INTO #One Values (123, 'Gather Mikes', 5)
    INSERT INTO #One Values (123, 'Gather Mikes', 2)
    INSERT INTO #One Values (123, 'Gather Mikes', 3)
    INSERT INTO #One Values (123, 'Gather Mikes', 6)
    INSERT INTO #One Values (128, 'Jeff Franks', 2)
    INSERT INTO #One Values (128, 'Jeff Franks', 6)
    INSERT INTO #One Values (128, 'Jeff Franks', 4)
    INSERT INTO #One Values (128, 'Jeff Franks', 12)
    INSERT INTO #One Values (128, 'Jeff Franks', 9)
    INSERT INTO #One Values (128, 'Jeff Franks', 3)
    INSERT INTO #One Values (128, 'Jeff Franks', 1)
     
    INSERT INTO #Two VALUES (123, 'Final', '', 'Gather Mikes')
    INSERT INTO #Two VALUES (123, 'Final', 'OK', 'Gather Mikes')
    INSERT INTO #Two VALUES (123, 'Complate', 'OK', 'Gather Mikes')
    INSERT INTO #Two VALUES (123, '', '', 'Gather Mikes')
    INSERT INTO #Two VALUES (128, 'Pending','','Jeff Franks')
    INSERT INTO #Two VALUES (128, 'Pending', '', 'Jeff Franks')
    INSERT INTO #Two VALUES (128, 'On Hold', '', 'Jeff Franks')
    INSERT INTO #Two VALUES (128, '', '', 'Jeff Franks')
    INSERT INTO #Two VALUES (128, 'Final', 'BL', 'Jeff Franks')
    INSERT INTO #Two VALUES (128, 'Complete', '', 'Jeff Franks')
    INSERT INTO #Two VALUES (128, '', '', 'Jeff Franks')
     
    ;With CTE
    As
    (
      SELECT
        BadgeNum
        ,NameOnFile
        ,SUM((CONVERT(decimal(18,6),pyrll.hoursworked))) AS [Hours]
      FROM
        #one pyrll
      Group By
        BadgeNum,NameOnFile
    )
    SELECT
    SUM(pyrll.[Hours]) As [Hours Worked This Week],
    pyrll.NameOnFile As [Employee Name],
    COUNT(case when pf.arrest_status in ('Final', 'Complete') And pf.supervisorSignoff IS NOT NULL THEN pf.ID else null end),
    COUNT(case when pf.arrest_status in ('Pending', 'Incomplete', 'On Hold') THEN pf.ID else null end)
    FROM #two pf
    INNER JOIN CTE pyrll
    ON pf.ID = pyrll.BadgeNum
    WHERE pf.officerName Like 'Gat%'
    GROUP BY pyrll.NameOnFile, pyrll.[Hours]
     
     
    --DROP TABLE #One
    --DROP TABLE #Two

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Remove the final grouping and review the data set returned - this should highlight the issue for you!

    Consider this code
    Code:
    ; WITH cte1 AS (
      SELECT BadgeNum
           , NameOnFile
           , Cast(Sum(hoursworked) As decimal(18,6)) As total_hours_worked
      FROM   #one
      GROUP
          BY BadgeNum
           , NameOnFile
    )
    , cte2 AS (
      SELECT ID As BadgeNum
           , Count(*) As total_entries
           , Sum(CASE WHEN arrest_status IN ('Final', 'Complete') AND supervisorSignoff IS NOT NULL THEN 1 ELSE 0 END) As total_complete
           , Sum(CASE WHEN arrest_status IN ('Pending', 'Incomplete', 'On Hold')                    THEN 1 ELSE 0 END) As total_incomplete
      FROM   #two
      GROUP
          BY ID
    )
    SELECT *
    FROM   cte1
     LEFT
      JOIN cte2
        ON cte2.BadgeNum = cte1.BadgeNum
    ;
    Ask any questions you might have!
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    @gvee Thank you for providing this - that is exactly what I was after and what I was needing! I now need to add a calendar to this and adding the calendar now shows multiple entries for each name which is making the list WAY longer than it should be. Probably just a join issue for someone advnaced like yourself. If you don't mind can you take a look and let me know what I did incorrectly?

    And if you need my ddl for the calendar I can provide that as well

    Code:
    ; WITH cte1 AS (
      SELECT BadgeNum
           , NameOnFile
           , Cast(Sum(hoursworked) As decimal(18,6)) As total_hours_worked
      FROM   #one
      GROUP
          BY BadgeNum
           , NameOnFile
    )
    , cte2 AS (
      SELECT ID As BadgeNum
           , clockInDay
           , Count(*) As total_entries
           , Sum(CASE WHEN arrest_status IN ('Final', 'Complete') AND supervisorSignoff IS NOT NULL THEN 1 ELSE 0 END) As total_complete
           , Sum(CASE WHEN arrest_status IN ('Pending', 'Incomplete', 'On Hold')                    THEN 1 ELSE 0 END) As total_incomplete
      FROM   #two
      GROUP
          BY ID, clockInDay
    )
    SELECT *
    FROM   cte1
     LEFT
      JOIN cte2
        ON cte2.BadgeNum = cte1.BadgeNum
    	INNER Join dailycalendar
    	ON cte2.clockInDay = dailycalendar.Day
    ;

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The query you have posted doesn't include a join to a calendar table... can you share what you've tried so far.
    Also, can you clarify what the calendar join is supposed to achieve?
    George
    Home | Blog

Posting Permissions

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