I need to calculate the total value of specific product codes. The product codes are 5780 and everything below 5700.
To calculate the value I use the code:
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
The ELSEpart is what relates to the product codes I need for this section of the report.
I tried using two different formulas and they both return different amounts but not the correct amount.
One is:
CASE WHEN [PC].[ProductCategoryCode] = 5780 OR [PC].[ProductCategoryCode] < 5700 THEN SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost] )
OVER ()
END
And the other is:
SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost]) OVER
(PARTITION BY (CASE WHEN [PC].[ProductCategoryCode] IN (5870, 5730, 5990)
THEN [PC].[ProductCategoryCode]
END)
)
They group the product codes correctly by displaying the same number for each, but the calculated number is incorrect.
Sample output:
Product Category Code | Value |
5870 | 100 |
5730 | 400 |
5990 | 200 |
5780 | 200 |
1111 | 50 |
2222 | 175 |
3333 | 500 |
4444 | 125 |
Total Value: 1050
It has to ignore product code 5870, 5990, and 5730 and not include
those in the total value.
I am using Microsoft SQL Sever 2005. Let me know if I need to explain further.
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]
, SUM(SUM(PL.OriginalQuantity_Stk)) OVER
(PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
THEN PC.ProductCategoryCode
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]
,
**SUM(Sum([PL].[OriginalQuantity_Stk]) / @Credits * [ICP].[UnitCost]) OVER
(PARTITION BY (CASE WHEN [PC].[ProductCategoryCode] IN (5870, 5730, 5990)
THEN [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 ()
, SUM(SUM(PL.OriginalQuantity_Stk)) OVER
(PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
THEN PC.ProductCategoryCode
END)
)
*Part of code I need assistance with.
Aucun commentaire:
Enregistrer un commentaire