Reputation: 105

Incorrect sum while calculating expression SQL

For a report that I am creating I need to find the Shrink. The shrink is basically the difference. It is the input weight (user generated) subtracted by the SUM of the total weight (OriginalQuantity_Stk)

Currently I am using the expression

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

I also tried to use

 Sum(@InputWeight - [WEIGHT]) AS [Shrink]

Because the grand total of the weight is already calculated:

Sum(IC_ProductLots.OriginalQuantity_Stk) AS [Weight]

When the user runs the report they enter some information and one piece of information is the input weight. For example we will say they enter 20,000. If the total weight adds up to 19,800, the Shrink should equal 200.

An example output would look like this:

Input Weight: 20,000
Product # | Weight | Shrink
1234 | 12,000 |
1235 | 6,500 |
1236 | 500 |
1237 | 800 |
--------------------------
TOTAL |19,800 | 200

Let me know if that makes sense or not.

I am using Microsoft SQL Sever 2005.

Full code for reference:

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

SET @PurchaseCost = 2.58;
SET @InputWeight = 20000;

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]
   , Sum(@InputWeight - [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-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 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))

Upvotes: 0

Answers (3)

xQbert

Reputation: 35333

Using a subquery should work. I still think a window function should work, but I'd have to play around with it more than I'm willing to without sample data/structure.

SELECT supplier
   , productcode
   , description1
   , boxes
   , unitofMeasure_Alt
   , weight
   , unitofMeasure_stk
   , [unit cost]
   , [total sales]
   , [avg. box weight]
   ,  yield/@inputweight as yield
   , @inputweight-shrink as shrink 
FROM (
SELECT 
     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] ) AS [Yield]
   , 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-24 00:00:00' }   AND (IC_ProductLots.ProductionDate <= { ts '2015-06-24 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)) B
 ORDER BY 
     Supplier

Upvotes: 0

Jason H

Reputation: 5156

I think you need to pull your 'logic' outside of your 'aggregate'

For example:

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

UPDATED: I ran this locally:

SELECT *
FROM #Test

Results:

Id      Weight
1234    12000
1235    6500
1236    500
1237    800

DECLARE @other INT = 1000;
SELECT
    Id,
    SUM([Weight])
FROM #Test
GROUP BY Id

Results:

Id      (No column name)
1234    12000
1235    6500
1236    500
1237    800

SELECT
    @other - SUM([Weight]) AS [Shrink]
FROM #Test
GROUP BY Id

Results:

Shrink
-11000
-5500
500
200

Upvotes: 0

Sean Lange

Reputation: 33581

Pretty sure that you want to take the sum of the weight minus the input value.

Sum([WEIGHT]) - @InputWeight AS [Shrink]

Or maybe it is

@InputWeight - Sum([WEIGHT]) AS [Shrink]

Upvotes: 3

Incorrect sum while calculating expression SQL

Answers (3)

Related Questions