I am using CASE to calculate the total weight of an item based on the product category.
To do that I am using the code:
CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END
AS [Total Weight]
5870, 5730, 5990 all display the correct total weight. The problem comes in with the ELSE part. The rest of the values all have different product category codes but they need to be grouped together and I need the total weight of those. Currently, the ELSE part is taking the total of all the items, including product 5870, 5730, 5990.
For example:
Product Category Code | Weight |
5870 | 100 |
5730 | 400 |
5990 | 200 |
1111 | 50 |
2222 | 175 |
3333 | 500 |
4444 | 125 |
5870 = 100
5730 = 400
5990 = 200
ELSE = 850
Whole Code:
SET NOCOUNT ON;
DECLARE @Credits Decimal(19,8);
DECLARE @Price Decimal(19,8);
SET @Credits = 41000;
SET @Price = 1.57;
SELECT
CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings'
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1]
END AS [Description]
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END
AS [Total Weight]
, Sum(CASE WHEN [PC].[ProductCategoryCode] <> 5870 THEN [PL].[OriginalQuantity_Stk]
WHEN [PC].[ProductCategoryCode] <> 5730 THEN [PL].[OriginalQuantity_Stk]
WHEN [PC].[ProductCategoryCode] <> 5990 THEN [PL].[OriginalQuantity_Stk]
END) AS [Weight]
, @Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER () AS [Shrink]
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE Sum([PL].[OriginalQuantity_Stk]) / @Credits
END AS [Yield]
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost]
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0
ELSE [ICP].[UnitCost]
END AS [UC]
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN ([ICP].[UnitCost] - 0.25) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN ([ICP].[UnitCost] * 0) * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE [ICP].[UnitCost] * SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits) OVER (PARTITION BY [PC].[ProductCategoryCode])
END AS [Value]
, CASE WHEN [PC].[ProductCategoryCode] = 5780 OR [PC].[ProductCategoryCode] < 5700 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost])
OVER (PARTITION BY [PC].[ProductCategoryCode])
END AS [TotalValue]
FROM (((( IC_Products [PC]
INNER JOIN DC_Transactions [DCT]
ON [PC].ProductKey = [DCT].ProductKey)
INNER JOIN AR_Customers [ARC]
ON [DCT].CustomerKey = [ARC].CustomerKey)
INNER JOIN IC_ProductLots [PL]
ON [DCT].LotKey = [PL].LotKey)
LEFT OUTER JOIN IC_ProductCosts [ICP]
ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
WHERE
([PL].ProductionDate >= { ts '2015-07-10 00:00:00' } AND ([PL].ProductionDate <= { ts '2015-07-10 00:00:00' } OR [PL].ProductionDate Is Null))
AND ((1=1) AND [ARC].CustomerKey IN (39) )
GROUP BY
CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN 'Trimmings'
WHEN [PC].[ProductCategoryCode] = 5730 THEN 'Rib Tips'
WHEN [PC].[ProductCategoryCode] = 5990 THEN 'Skins'
ELSE [PC].[Description1]
END
, [ARC].CustomerKey
, [PC].ProductCategoryCode
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN [ICP].[UnitCost]
WHEN [PC].[ProductCategoryCode] = 5730 THEN [ICP].[UnitCost] - 0.25
WHEN [PC].[ProductCategoryCode] = 5990 THEN [ICP].[UnitCost] * 0
ELSE [ICP].[UnitCost]
END
, [ICP].UnitCost
, [PC].ProcessCode
ORDER BY
@Credits - SUM(Sum([PL].[OriginalQuantity_Stk])) OVER ()
, CASE WHEN [PC].[ProductCategoryCode] = 5870 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5730 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
WHEN [PC].[ProductCategoryCode] = 5990 THEN SUM(SUM([PL].[OriginalQuantity_Stk])) OVER (PARTITION BY [PC].[ProductCategoryCode])
ELSE SUM(SUM([PL].[OriginalQuantity_Stk])) OVER()
END
I am using Microsoft SQL Sever 2005.
Aucun commentaire:
Enregistrer un commentaire