组总数的百分比

Question

我正在尝试添加一个新列来计算当周每个包装类型的总计百分比。

以下代码的结果如下。

所以我想新的列中显示升总数的百分比为特定的星期。

代码：

SET DATEFIRST 1 

SELECT 

DATEPART (wk, t0.U_ORC_BE_ProdDate) AS [Week Produced], 
--Display week number of date produced 
(
CASE 
    WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' 
     THEN 'Cans' 
    WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' 
     THEN 'Bottles' 
    WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' 
     THEN 'Key Keg' 
    WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' 
     THEN 'SS Keg' 
    WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' 
     Then 'SS Keg' 
END 
)AS [Pack Type], 
--Collate item types to pack types 
sum(t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) AS [Total Hectoliters] 
--Calculate total HL in the order 


FROM OWOR T0 
INNER JOIN OITT T1 ON T0.ItemCode = T1.Code 
INNER JOIN OITM T2 ON T1.Code = T2.ItemCode 
LEFT JOIN [@ORC_BE_PACK_TYPE] t5 ON t5.Code = t2.U_ORC_BE_PACK_TYPE 
LEFT JOIN [@ORC_BE_STYLE_H] t6 ON t6.Code= t2.U_ORC_BE_ShortCode 

WHERE 
    (t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) != 0 
AND U_ORC_BE_ProcessType = 'Packaging' 
AND 
    (
    T0.[U_ORC_BE_ProdDate] >= dateadd(wk, datediff(wk, 0, getdate()) - 13, 0) 
    AND T0.[U_ORC_BE_ProdDate] < dateadd(wk, datediff(wk, 0, getdate()), 0) 
    ) 
--For previous 12 weeks 

GROUP BY datepart(wk, t0.U_ORC_BE_ProdDate), 

    t0.U_Operator, 
    (
    CASE 
     WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' 
      THEN 'Cans' 
     WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' 
      THEN 'Bottles' 
     WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' 
      THEN 'Key Keg' 
     WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' 
      THEN 'SS Keg' 
     WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' 
      Then 'SS Keg' 
    END 
    ) 

ORDER BY datepart (wk, t0.U_ORC_BE_ProdDate) ASC 

Answer 1

使用SUM（）OVER（）聚合全额每个日历单元和做的百分比计算这一点。

SELECT 
     d.* 
    , ([Total Hectoliters] * 100.0)/SUM([Total Hectoliters]) OVER (PARTITION BY [Week Produced]) [Weekly Pct] 
FROM (
     SELECT 
      DATEPART(wk, t0.U_ORC_BE_ProdDate)  AS [Week Produced] 
      --Display week number of date produced 
      , (
      CASE 
        WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' THEN 'Cans' 
        WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' THEN 'Bottles' 
        WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' THEN 'Key Keg' 
        WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' THEN 'SS Keg' 
        WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' THEN 'SS Keg' 
      END 
      )           AS [Pack Type] 
      --Collate item types to pack types 
      , SUM(t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) AS [Total Hectoliters] 
     --Calculate total HL in the order 
     FROM OWOR T0 
      INNER JOIN OITT T1 ON T0.ItemCode = T1.Code 
      INNER JOIN OITM T2 ON T1.Code = T2.ItemCode 
      INNER JOIN [@ORC_BE_PACK_TYPE] t5 ON t5.Code = t2.U_ORC_BE_PACK_TYPE 
      LEFT JOIN [@ORC_BE_STYLE_H] t6 ON t6.Code = t2.U_ORC_BE_ShortCode 
     WHERE (t5.U_ORC_BE_HECTOLITER * t0.CmpltQty) <> 0 
      AND U_ORC_BE_ProcessType = 'Packaging' 
      --For previous 12 weeks 
      AND (T0.[U_ORC_BE_ProdDate] >= DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) - 13, 0) 
      AND T0.[U_ORC_BE_ProdDate] < DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0) 
      ) 
     GROUP BY 
      DATEPART(wk, t0.U_ORC_BE_ProdDate) 
      , t0.U_Operator 
      , (
      CASE 
        WHEN t5.U_ORC_BE_NAME LIKE '%Cans%' THEN 'Cans' 
        WHEN t5.U_ORC_BE_NAME LIKE '%Bottles%' THEN 'Bottles' 
        WHEN t5.U_ORC_BE_NAME LIKE '%Key Keg%' THEN 'Key Keg' 
        WHEN t5.U_ORC_BE_NAME LIKE '%SS Keg%' THEN 'SS Keg' 
        WHEN t5.U_ORC_BE_NAME LIKE '%E-Keg%' THEN 'SS Keg' 
      END 
      ) 
) AS d 
ORDER BY 
     [Week Produced] ASC 

顺便说一句，因为你的where子句中坚持认为，别名T5满足某些条件下存在使用没有留下一点上T5加入。也就是说，因为每一行都必须满足这些条件，所以t5的NULL结果将被忽略，因此外连接对该表无关紧要。

Answer 2

通过查看您的表格截图，我只写了这个查询，而没有深入到您提供的查询中。不知道这是你想要的最终结果。如果没有，您可能需要将其与您的查询合并:)

SELECT z.WeekProduced, z.PackType, z.packTotal, (z.packTotal/z.WeekTotal) as Percentage 
FROM 
((SELECT WeekProduced, PackType, SUM(Totalhectoliters) as packTotal 
FROM GivenTable 
GROUP BY WeekProduced, PackType) AS x INNER JOIN    
(SELECT WeekProduced, SUM(Totalhectoliters) as WeekTotal 
FROM GivenTable 
GROUP BY WeekProduced) AS y 
ON x.WeekProduced = y.WeekProduced) AS z