Dear all,
I have the follwoing SQL server store procedure that I have tested and created under my local SQL server 2012
CREATE PROCEDURE [dbo].[RPT_GetRemainingStockByProduct]
-- Add the parameters for the stored procedure here
@StoreID int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT *,
SUM(Remaining) OVER (PARTITION BY StoreID ORDER BY ProductID) AS TotalRemainingAtStore
FROM
(
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
SUM(PIT.Quantity * P.ItemUnitWeight)-SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS Remaining,
Production.UnitMeasure.UnitMeasureCode,
Sales.Store.Name,
LOC.MaxQuantity as MaxStorage
FROM Sales.Store INNER JOIN
Production.ProductItem AS PIT INNER JOIN
Production.Product AS P ON PIT.ProductID = P.ProductID INNER JOIN
Production.UnitMeasure ON P.WeightUnitMeasureID = Production.UnitMeasure.UnitMeasureID ON Sales.Store.BusinessEntityID = P.StoreID
INNER JOIN
Production.Location AS LOC ON Sales.Store.BusinessEntityID = LOC.StoreID AND P.LocationID = LOC.LocationID AND P.LocationID = LOC.LocationID
WHERE (P.IsDeleted = 0) AND (PIT.IsDeleted = 0) AND P.StoreID=@StoreID
GROUP BY P.StoreID,P.InventoryDate, P.ProductID, Production.UnitMeasure.UnitMeasureCode,Sales.Store.Name,LOC.MaxQuantity
)t
END
When trying to create that store procs under my SQL azure db, I get an error message saying that ORDER BY is not allowed in line : SUM(Remaining) OVER (PARTITION BY StoreID ORDER BY ProductID) AS TotalRemainingAtStore
Any idea ?
regards
