mardi 28 juillet 2015

Selecting SUM of specific values but not altering report

For my report I have several different product codes. These product codes make up two different sections. 5870, 5730, and 5990 are one section (top half) and 5780 and everything less than 5700 make up another section (bottom half). I already have a code that combines 5780 and everything less than 5700 and that is:

SUM(SUM(PL.OriginalQuantity_Stk)) OVER
       (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
                           THEN PC.ProductCategoryCode
                      END)
       ) 

A sample of this output is:

Product Category Code | Weight |
5870                  | 100    |
5730                  | 400    |
5990                  | 200    |
5780                  | 50     | 
1111                  | 50     |  
2222                  | 175    |  
3333                  | 500    |  
4444                  | 125    |  

5870 = 100
5730 = 400
5990 = 200
Other= 900

That code does exactly what I need it to do for that part of the report. However, that code also generates the SUM of the other product codes which is great for that part of the report but for another calculation, I only need to use the product codes 5780 and everything less than 5700.

Right now I am trying to use the code:

(CASE WHEN PC.ProductCategoryCode = 5780 OR PC.ProductCategoryCode < 5700 THEN Sum(Sum(PL.OriginalQuantity_Stk)) OVER(PARTITION BY PC.ProductCategoryCode)
ELSE 0
 END)

This makes the irrelevant product code's weight equal to zero but it doesn't take the total SUM of the product code's weight I need.

An example output of what I need now is:

Product Category Code | Weight |
5870                  | 100    |
5730                  | 400    |
5990                  | 200    |
5780                  | 50     | 
1111                  | 50     |  
2222                  | 175    |  
3333                  | 500    |  
4444                  | 125    |  

5870 = 0
5730 = 0
5990 = 0
Other= 900

It doesn't necessarily have to equal 0, but I only need the 900 value because I will have to use that 900 for other calculations in this report. I hope that makes sense. Let me know if I need to clarify more.

I am using Microsoft SQL Server 2005.

Whole Code:

SET NOCOUNT ON; 
DECLARE @Credits Decimal(19,8);
DECLARE @Price Decimal(19,8);
DECLARE @Labor Decimal(19,8);
DECLARE @Packaging Decimal(19,8);
DECLARE @Overhead Decimal(19,8);

SET @Credits = 41000;
SET @Price = 1.57;
SET @Labor  = 0;
SET @Packaging  = 0;
SET @Overhead  = 0;

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(PL.OriginalQuantity_Stk) / @Credits) 
 WHEN PC.ProductCategoryCode = 5730
 THEN (ICP.UnitCost - 0.25) * (Sum(PL.OriginalQuantity_Stk) / @Credits)
 WHEN PC.ProductCategoryCode = 5990
 THEN (ICP.UnitCost * 0) * (Sum(PL.OriginalQuantity_Stk) / @Credits) 
 ELSE ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits) 
 END AS [Value]
   , SUM(ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
) AS [Total Value]
   , (CASE WHEN PC.ProductCategoryCode = 5780 OR PC.ProductCategoryCode < 5700 THEN Sum(Sum(PL.OriginalQuantity_Stk)) OVER(PARTITION BY PC.ProductCategoryCode)
ELSE 0
 END) AS [Test]
 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
   , [PC].ProductCategoryCode
   , [ARC].CustomerKey
   , 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)
) 
   , SUM(ICP.UnitCost * (Sum(PL.OriginalQuantity_Stk) / @Credits)) OVER
 (PARTITION BY (CASE WHEN PC.ProductCategoryCode IN (5870, 5730, 5990)
 THEN PC.ProductCategoryCode
 END)
)

Aucun commentaire:

Enregistrer un commentaire