Forum Discussion

Davem's avatar
Davem
Frequent Visitor
6 years ago

Direct query _Mid suffix results in count

Hi there,

I am using direct query to pull in various shift measures where each measure ends with a shift reference. When the data loads all of my _Am, _PM and _WKD measures get correctly set as Sum but all of my _MID references get set as Count? Is this expected behaviour? It's a pain to have to manually adjust to sum and rename 😄

 

Here is an example of my SQL, taskid and shiftid are Int fields, Hours is a float field

 

SELECT TD.Ddate
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS KitHrs_AM
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS KitHrs_PM
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS KitHrs_MID
, SUM(CASE WHEN TD.[taskid] = 1 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS KitHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_AM
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_PM
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_MID
, SUM(CASE WHEN TD.[taskid] = 3 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS KitAuditHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_AM
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_PM
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_MID
, SUM(CASE WHEN TD.[taskid] = 2 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS MoveOrderHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_AM
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_PM
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_MID
, SUM(CASE WHEN TD.[taskid] = 61 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS MSRHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_AM
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_PM
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_MID
, SUM(CASE WHEN TD.[taskid] = 5 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS STN27Hrs_WKD
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS DockHrs_AM
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS DockHrs_PM
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS DockHrs_MID
, SUM(CASE WHEN TD.[taskid] = 4 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS DockHrs_WKD
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 1 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_AM
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 2 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_PM
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 3 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_MID
, SUM(CASE WHEN TD.[taskid] = 6 And TD.[shiftID] = 4 THEN TD.[Hours] ELSE 0 END) AS SPECIALPROHrs_WKD
FROM tblTaskDetail TD
GROUP BY Ddate

Need Help

2 Replies

    • Davem's avatar
      Davem
      Frequent Visitor
      6 years ago

      Hi thanks for the reply, I am just trying to total hours and activity counts based on tasks completed in our facility. If I use the SQL below to pull in sums from SQL Server 2017, MoveOrder_AM, MoveOrder_PM & MoveOrder_WKD all correctly load into Power BI as summed values but the MID reference causes that field to count for some reason. If I change the SQL AS reference for that field to be anything other than MID ie MoveOrder_MD, it works correctly. I just thought it was odd that the MID reference would cause this behaviour?

       

      SELECT TD.Ddate
      , SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 1 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_AM
      , SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 2 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_PM
      , SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 3 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_MID
      , SUM(CASE WHEN TD.[taskid] = 2 AND TD.[ShiftID] = 4 THEN TD.[linestickets] ELSE 0 END) AS MoveOrder_WKD
      FROM tblTaskDetail TD
      GROUP BY TD.Ddate;