When attempting to calculate an expression, I get the error Incorrect syntax near the keyword 'OVER'.
I'm not sure what is the error on this line:
(SUM([DCT].[Quantity_Stk] * [ICP].[UnitCost] )) - ((@PurchaseCost + @Prod_CostLBS ) * @InputWeight ) OVER (PARTITION BY [ARC].[CustomerCode] )
I was able to use OVER on another expression and it worked just fine and was formatted like the one above, except it was only taking the SUM of one column.
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]
, 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]
, SUM([DCT].[Quantity_Stk] * [ICP].[UnitCost]) - ((@PurchaseCost + @Prod_CostLBS) * @InputWeight) OVER (PARTITION BY [ARC].[CustomerCode])
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