Aggregation Issue in SQL Server

My query is pivoted twice in order to get the sum of downtime codes in each column and the count of downtime codes in each column.

There are two inner joins to bring in the width and a qty component to perform a calculation for SQM produced and LM produced.

My issue is that my query is getting duplicated records and I feel like I've tried everything to fix it; I need one record for each Date, Machine, LM(produced), SQM(Produced) and each count ( NUM_ ) and sum ( H_ ) for downtime codes.

Data output example: https://docs.google.com/spreadsheets/d/1lnQrtP5GO-F_zB7zR2GBvLe8RIa61rA8/edit?usp=sharing&ouid=115258646474729421097&rtpof=true&sd=true

SELECT 
  Pivot_DownTime_Code.Date, 
  Pivot_DownTime_Code.Mach AS MACHINE, 
  FORMAT(
    ISNULL(
      ROUND(
        (
          SUM(Table2.qty) / 3.2808399
        ), 
        4
      ), 
      0
    ), 
    'F', 
    'it-IT'
  ) AS LM_CY, 
  FORMAT(
    ROUND(
      SUM(
        (
          (
            ROUND(
              (Table2.qty / 12), 
              4
            ) * (
              CONVERT(FLOAT, Table3.Width)
            )
          ) / 10.7639104
        ) -1
      ), 
      4
    ), 
    'F', 
    'it-IT'
  ) AS SQM_CY, 
  Pivot_DownTime_Code.[Reason1] AS H_Reason1, 
  Pivot_DownTime_Code.[Reason2] AS H_Reason2, 
  Pivot_DownTime_Code.[Reason3] AS H_Reason3, 
  Pivot_DownTime_Code.[Reason4] AS H_Reason4, 
  Pivot_DownTime_Code.[Reason5] AS H_Reason5, 
  Pivot_DownTime_Code.[Reason6] AS H_Reason6, 
  Pivot_DownTime_Code.[Reason7] AS H_Reason7, 
  Pivot_DownTime_Code.[Reason8] AS H_Reason8, 
  Pivot_DownTime_Code.[Reason9] AS H_Reason9, 
  Pivot_DownTime_Code.[Reason10] AS H_Reason10, 
  Pivot_DownTime_Code.[Reason11] AS H_Reason11, 
  Pivot_DownTime_Code.[Reason12] AS H_Reason12, 
  Pivot_DownTime_Code.[Reason13] AS H_Reason13, 
  Pivot_DownTime_Code.[Reason14] AS H_Reason14, 
  NUM_Reason1, 
  NUM_Reason2, 
  NUM_Reason3, 
  NUM_Reason4, 
  NUM_Reason5, 
  NUM_Reason6, 
  NUM_Reason7, 
  NUM_Reason8, 
  NUM_Reason9, 
  NUM_Reason10, 
  NUM_Reason11, 
  NUM_Reason12, 
  NUM_Reason13, 
  NUM_Reason14 
FROM 
  (
    SELECT 
      Date, 
      Mach, 
      DownMinutes, 
      ISNULL(_Code, 'Reason14') AS _Code, 
-- Here downtime codes are being counted
      SUM(
        CASE WHEN _Code = 'Reason1' THEN 1 ELSE 0 END
      ) AS NUM_Reason1, 
      SUM(
        CASE WHEN _Code = 'Reason2' THEN 1 ELSE 0 END
      ) AS NUM_Reason2, 
      SUM(
        CASE WHEN _Code = 'Reason3' THEN 1 ELSE 0 END
      ) AS NUM_Reason3, 
      SUM(
        CASE WHEN _Code = 'Reason4' THEN 1 ELSE 0 END
      ) AS NUM_Reason4, 
      SUM(
        CASE WHEN _Code = 'Reason5' THEN 1 ELSE 0 END
      ) AS NUM_Reason5, 
      SUM(
        CASE WHEN _Code = 'Reason6' THEN 1 ELSE 0 END
      ) AS NUM_Reason6, 
      SUM(
        CASE WHEN _Code = 'Reason7' THEN 1 ELSE 0 END
      ) AS NUM_Reason7, 
      SUM(
        CASE WHEN _Code = 'Reason8' THEN 1 ELSE 0 END
      ) AS NUM_Reason8, 
      SUM(
        CASE WHEN _Code = 'Start-up/Shutdown' THEN 1 ELSE 0 END
      ) AS NUM_Reason9, 
      SUM(
        CASE WHEN _Code = 'Reason10' THEN 1 ELSE 0 END
      ) AS NUM_Reason10, 
      SUM(
        CASE WHEN _Code = 'Reason11' THEN 1 ELSE 0 END
      ) AS NUM_Reason11, 
      SUM(
        CASE WHEN _Code = 'Reason12' THEN 1 ELSE 0 END
      ) AS NUM_Reason12, 
      SUM(
        CASE WHEN _Code = 'Reason13' THEN 1 ELSE 0 END
      ) AS NUM_Reason13, 
      SUM(
        CASE WHEN _Code = 'Reason14' THEN 1 ELSE 0 END
      ) AS NUM_Reason14 
    FROM 
      Database1.dbo.Table1
    GROUP BY 
      Date, 
      Mach, 
      DownMinutes, 
      _Code
  ) AS DownTimeTotals PIVOT(
  
--This has to be pivoted for the desired layout

    SUM([DownMinutes]) FOR [_Code] IN(
      [Reason1], [Reason2], 
      [Reason3], [Reason4], 
      [Reason5], [Reason6], 
      [Reason7], [Reason8], 
      [Reason9], [Reason10], 
      [Reason11], [Reason12], 
      [Reason13], [Reason14]
    )
  ) AS Pivot_DownTime_Code 
  INNER JOIN [Server1].[Database1].dbo.Table2 ON Pivot_DownTime_Code.Mach = Table2.coater_desc 
  AND CONVERT(
    VARCHAR, Pivot_DownTime_Code.Date, 
    1
  ) = CONVERT(VARCHAR, Table2.picked_date, 1) 
  INNER JOIN [Server1].[Database1].dbo.Table3 ON Pivot_DownTime_Code.Mach = Table3.Machine 
  AND Table2.[MO Number] = Table3.MO 
  AND CONVERT(
    VARCHAR, Pivot_DownTime_Code.Date, 
    1
  ) = CONVERT(VARCHAR, Table3.Date, 1) 
GROUP BY 
  Pivot_DownTime_Code.Date, 
  Pivot_DownTime_Code.Mach, 
  Table3.Width, 
  Pivot_DownTime_Code.[Reason1], 
  Pivot_DownTime_Code.[Reason2], 
  Pivot_DownTime_Code.[Reason3], 
  Pivot_DownTime_Code.[Reason4], 
  Pivot_DownTime_Code.[Reason5], 
  Pivot_DownTime_Code.[Reason6], 
  Pivot_DownTime_Code.[Reason7], 
  Pivot_DownTime_Code.[Reason8], 
  Pivot_DownTime_Code.[Reason9], 
  Pivot_DownTime_Code.[Reason10], 
  Pivot_DownTime_Code.[Reason11], 
  Pivot_DownTime_Code.[Reason12], 
  Pivot_DownTime_Code.[Reason13], 
  Pivot_DownTime_Code.[Reason14], 
  NUM_Reason1, 
  NUM_Reason2, 
  NUM_Reason3, 
  NUM_Reason4, 
  NUM_Reason5, 
  NUM_Reason6, 
  NUM_Reason7, 
  NUM_Reason8, 
  NUM_Reason9, 
  NUM_Reason10, 
  NUM_Reason11, 
  NUM_Reason12, 
  NUM_Reason13, 
  NUM_Reason14 
ORDER BY 
  Date;

Thank you everyone for the assistance. All look like good explanations. I ended up:

inserting the output of the date, machine, LM, SQM into a temp table
Joined that output on the first pivot of the sum of down minutes
Joined that temp table on the next pivoted query

For anyone else looking at this question from a search:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
DROP 
  TABLE #TempTable
  GO IF OBJECT_ID('tempdb..#TempTable2') IS NOT NULL 
DROP 
  TABLE #TempTable2
  GO 
SELECT 
  ...INTO #TempTable
FROM 
  ...
  JOIN...(if needed) GO 
SELECT 
  ...
FROM 
  ...(PIVOT Statement) GO 
SELECT 
  #Temptable2.FieldNames...
  SUM (
    CASE WHEN _CODE = 'Reason1' then 1 else 0 end
  ) as Num_REASON1...
FROM 
  TABLE1 
  INNER JOIN #TempTable2 etc

Aggregation Issue in SQL Server

1条答案

相关问题

热门标签

最新问答