mardi 14 juillet 2015

Error with aggregate functions

I've been having several problems using OVER and CAST while attempting to do aggregate calculations for my report.

I was able to get help for two of my previous calculations and now I am having trouble with the third. This time I am trying to calculate the profit. To do this I need to use the "Total Sales" which is [DCT].[Quantity_Stk] *[ICP].[UnitCost]and subtract that by the "Cost" which is (@PurchaseCost + @Prod_CostLBS) * @InputWeight

Currently I am using the expression:

CAST (([DCT].[Quantity_Stk] *[ICP].[UnitCost]) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2)) AS [PROFIT]

With this I get the error message: The same Query View cannot show a column with a Group designation of 'Expression' without an aggregate function when another column contains a Group designation of 'Group By' Previously, I was able to display the report without an error but the value was completely off.

I also tried:

CAST (SUM(Sum([DCT].[Quantity_Stk] *[ICP].[UnitCost])) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2))

I need it to an aggregate function.

Here is my full code:

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

SET @PurchaseCost = 2.58;
SET @InputWeight = 18100;
SET @Prod_CostLBS  = .15;

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]
   , [ARC].CustomerKey
   , Sum([DCT].[Quantity_Stk] *[ICP].[UnitCost]) AS [Total Sales]
   , Avg(([IC_ProductLots].[OriginalQuantity_Stk] / [IC_ProductLots].[OriginalQuantity_Alt])) AS [Avg. Box Weight]
   , Sum([IC_ProductLots].[OriginalQuantity_Stk] / @InputWeight) AS [Yield]
   , CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2)) AS [Shrink]
   , Max(CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2))) AS [COST]
   , Max(CAST (([DCT].[Quantity_Stk] *[ICP].[UnitCost]) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER () AS DECIMAL(18,2))
) AS [Profit]
 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 ((1=1)  AND [ARC].CustomerKey IN (124) ) 
 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].CustomerKey
 ORDER BY 
     CAST([ARC].[CustomerCode] AS NVARCHAR(40)) + ' - ' + CAST([ARC].[Name] AS NVARCHAR(40)) 
   , CAST (@InputWeight - SUM(Sum([IC_ProductLots].[OriginalQuantity_Stk])) OVER () AS DECIMAL(18,2)) 
   , Max(CAST ((@PurchaseCost + @Prod_CostLBS) * @InputWeight AS DECIMAL (18,2)))

I am using Microsoft SQL Server 2005.

Aucun commentaire:

Enregistrer un commentaire