lundi 13 juillet 2015

SQL code not using total SUM

For a report I am creating I need to take the calculated SUM and use that for another equation. The equation is used to calculate the Shrink, which is the same thing as the difference. To calculate the Shrink I use:

@InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]

The input weight is given by the user before running the report. So for example, if the input weight is 15,000 and the SUM of the original quantity is 14,800 then the Shrink should be 200.

The problem is that that code is calculating the Shrink for each row and each report should only have ONE Shrink.

An example output would look like this:

Input Weight: 15,000
Product # | Weight | Shrink
1234      | 7,000  | 
1235      | 6,500  | 
1236      | 500    | 
1237      | 800    | 
--------------------------
TOTAL     |14,800  | 200 

I am using Microsoft SQL Server 2005.

Below is my full code if that helps:

SET NOCOUNT ON; 
DECLARE @PurchaseCost Decimal(19,8);
DECLARE @InputWeight Decimal(19,8);

SET @PurchaseCost = 1;
SET @InputWeight = 15000;
    SELECT DISTINCT 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) AS [Supplier]
       , [PC].ProductCode
       , [PC].Description1
       , Count(IC_ProductLots.OriginalQuantity_Alt) AS [Boxes]
       , IC_ProductLots.UnitOfMeasure_Alt
       , Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]
       , IC_ProductLots.UnitOfMeasure_Stk
       , [ICP].UnitCost AS [Unit Cost]
       , Sum(ROUND([DCT].[Quantity_Stk] *[ICP].[UnitCost], 2)) AS [Total Sales]
       , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
       , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
       , @InputWeight - Sum([IC_ProductLots].[OriginalQuantity_Stk]) AS [Shrink]
     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 
         ON [DCT].LotKey = IC_ProductLots.LotKey)
        LEFT OUTER JOIN  IC_ProductCosts [ICP] 
         ON ICP.ProductKey=PC.ProductKey and ICP.ProductCostCode=5)
     WHERE 
        (IC_ProductLots.ProductionDate >= { ts '2015-06-29 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-29 00:00:00' } OR IC_ProductLots.ProductionDate Is Null)) 
    AND ([ARC].CustomerCode = '         904') 
     GROUP BY 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))
       , [PC].ProductCode
       , [PC].Description1
       , IC_ProductLots.UnitOfMeasure_Alt
       , IC_ProductLots.UnitOfMeasure_Stk
       , [ICP].UnitCost
       , IC_ProductLots.ProductionDate
       , [ARC].CustomerCode
     ORDER BY 
         CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40))

Basically, I need to calculate the total shrink and not a shrink for each individual row.

Aucun commentaire:

Enregistrer un commentaire