Author |
Topic |
Anushka
Yak Posting Veteran
79 Posts |
Posted - 2008-04-25 : 05:09:03
|
actually when i was trying to post this in previous tread it is not taking that's y in new tread iam sending this query........ALTER PROCEDURE enterprise.NonMovingItemsList --'FINANCE','DEFAULT','DEFAULT','4/29/2008'(@CompanyID NVARCHAR(36),@DivisionID NVARCHAR(36),@DepartmentID NVARCHAR(36),@Date SMALLDATETIME)ASSET NOCOUNT ONDECLARE @tblTemp2 AS TABLE ( receiveddate SMALLDATETIME,PURCHASENUMBER NVARCHAR(36),itemid NVARCHAR(36),Description NVARCHAR(80),ItemUnitPrice Money,Subtotal Money,ReceivedQty float,QtyOnHand float,Age int);WITH Control (ItemID, Description, ReceivedQty, ReceivedDate, RecID)AS (SELECT ItemID,[Description],ReceivedQty,ReceivedDate,ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY ReceivedDate DESC)FROM PurchaseDetailWHERE CompanyID = @CompanyIDAND DivisionID = @DivisionIDAND DepartmentID = @DepartmentIDAND ReceivedQty > 0), Yak (ItemID, rQtyLow, rQtyHigh, ReceivedDate, RecID)AS (SELECT ItemID,CAST(0 AS INT),CAST(ReceivedQty AS INT),ReceivedDate,RecIDFROM ControlWHERE RecID = 1UNION ALLSELECT y.ItemID,CAST(y.rQtyHigh AS INT),CAST(y.rQtyHigh + c.ReceivedQty AS INT),c.ReceivedDate,c.RecIDFROM Yak AS yINNER JOIN Control AS c ON c.RecID = y.RecID + 1WHERE y.ItemID = c.ItemID)INSERT INTO @tblTemp2SELECT pd.ReceivedDate AS [Last Received Date],pd.PurchaseNumber AS [Ref Doc#],pd.ItemID AS [Item ID],pd.Description,pd.ItemUnitPrice AS Rate,pd.Subtotal AS [Value],pd.ReceivedQty AS [Received Qty],iw.QtyOnHand AS [Stock As On Date],DATEDIFF(DAY, y.ReceivedDate, @Date) AS AgeFROM Yak AS yINNER JOIN PurchaseDetail AS pd ON pd.ItemID = y.ItemIDINNER JOIN InventoryByWarehouse AS iw ON iw.ItemID = pd.ItemIDAND iw.CompanyID = pd.CompanyIDAND iw.DivisionID = pd.DivisionIDAND iw.DepartmentID = pd.DepartmentIDAND iw.QtyOnHand > 0WHERE pd.CompanyID = @CompanyIDAND pd.DivisionID = @DivisionIDAND pd.DepartmentID = @DepartmentIDAND iw.QtyOnHand > y.rQtyLowAND iw.QtyOnHand <= y.rQtyHighORDER BY pd.ItemIDSELECT receiveddate AS [Last Received Date],PURCHASENUMBER AS [Ref Doc#],itemid AS [Item ID],DESCRIPTION AS [Description],ITEMUNITPRICE as [Rate],SUBTOTAL as [Value],ReceivedQty as [Received Qty],QtyOnHand as [Stock As On Date],AgeFROM(SELECT ROW_NUMBER() OVER( PARTITION BY ITEMID Order BY receiveddate DESC) AS RowNo,receiveddate,PURCHASENUMBER,ITEMID,DESCRIPTION,ITEMUNITPRICE,SUBTOTAL,ReceivedQty,QtyOnHand,AgeFROM @tblTemp2(Incorrect syntax near '@tblTemp2'.) |
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-25 : 05:23:28
|
[code]ALTER PROCEDURE enterprise.NonMovingItemsList --'FINANCE','DEFAULT','DEFAULT','4/29/2008'( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @Date SMALLDATETIME)ASSET NOCOUNT ON;WITH Control (ItemID, Description, ReceivedQty, ReceivedDate, RecID)AS ( SELECT ItemID, [Description], ReceivedQty, ReceivedDate, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY ReceivedDate DESC) FROM PurchaseDetail WHERE CompanyID = @CompanyID AND DivisionID = @DivisionID AND DepartmentID = @DepartmentID AND ReceivedQty > 0), Yak (ItemID, rQtyLow, rQtyHigh, ReceivedDate, RecID)AS ( SELECT ItemID, CAST(0 AS INT), CAST(ReceivedQty AS INT), ReceivedDate, RecID FROM Control WHERE RecID = 1UNION ALL SELECT y.ItemID, CAST(y.rQtyHigh AS INT), CAST(y.rQtyHigh + c.ReceivedQty AS INT), c.ReceivedDate, c.RecID FROM Yak AS y INNER JOIN Control AS c ON c.RecID = y.RecID + 1 WHERE y.ItemID = c.ItemID)SELECT [Last Received Date], [Ref Doc#], [Item ID], [Description], Rate, [Value], [Received Qty], [Stock As On Date], AgeFROM ( SELECT ROW_NUMBER() OVER( PARTITION BY pd.ItemID ORDER BY pd.ReceivedDate DESC) AS RowNo, pd.ReceivedDate AS [Last Received Date], pd.PurchaseNumber AS [Ref Doc#], pd.ItemID AS [Item ID], pd.Description, pd.ItemUnitPrice AS Rate, pd.Subtotal AS [Value], pd.ReceivedQty AS [Received Qty], iw.QtyOnHand AS [Stock As On Date], DATEDIFF(DAY, y.ReceivedDate, @Date) AS Age FROM Yak AS y INNER JOIN PurchaseDetail AS pd ON pd.ItemID = y.ItemID INNER JOIN InventoryByWarehouse AS iw ON iw.ItemID = pd.ItemID AND iw.CompanyID = pd.CompanyID AND iw.DivisionID = pd.DivisionID AND iw.DepartmentID = pd.DepartmentID AND iw.QtyOnHand > 0 WHERE pd.CompanyID = @CompanyID AND pd.DivisionID = @DivisionID AND pd.DepartmentID = @DepartmentID AND iw.QtyOnHand > y.rQtyLow AND iw.QtyOnHand <= y.rQtyHigh ) AS dWHERE RowNo = 1ORDER BY [Item ID][/code] E 12°55'05.25"N 56°04'39.16" |
|
|
Anushka
Yak Posting Veteran
79 Posts |
Posted - 2008-04-25 : 05:27:44
|
Msg 156, Level 15, State 1, Procedure NonMovingItemsList, Line 79Incorrect syntax near the keyword 'WHERE'.near Last but 1 line error - WHERE RowNo = 1 |
|
|
Anushka
Yak Posting Veteran
79 Posts |
Posted - 2008-04-25 : 08:12:41
|
Thank u vvvvvvvvvvv much.........................both of u... |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-25 : 08:14:11
|
Is it working now? E 12°55'05.25"N 56°04'39.16" |
|
|
Anushka
Yak Posting Veteran
79 Posts |
Posted - 2008-04-25 : 08:18:31
|
JUST I HAD DONE COPY PASTE...CAN U EXPLANE ME THIS LOGIC PLS...........quote: Originally posted by Peso
ALTER PROCEDURE enterprise.NonMovingItemsList --'FINANCE','DEFAULT','DEFAULT','4/29/2008'( @CompanyID NVARCHAR(36), @DivisionID NVARCHAR(36), @DepartmentID NVARCHAR(36), @Date SMALLDATETIME)ASSET NOCOUNT ON;WITH Control (ItemID, Description, ReceivedQty, ReceivedDate, RecID)AS ( SELECT ItemID, [Description], ReceivedQty, ReceivedDate, ROW_NUMBER() OVER (PARTITION BY ItemID ORDER BY ReceivedDate DESC) FROM PurchaseDetail WHERE CompanyID = @CompanyID AND DivisionID = @DivisionID AND DepartmentID = @DepartmentID AND ReceivedQty > 0), Yak (ItemID, rQtyLow, rQtyHigh, ReceivedDate, RecID)AS ( SELECT ItemID, CAST(0 AS INT), CAST(ReceivedQty AS INT), ReceivedDate, RecID FROM Control WHERE RecID = 1UNION ALL SELECT y.ItemID, CAST(y.rQtyHigh AS INT), CAST(y.rQtyHigh + c.ReceivedQty AS INT), c.ReceivedDate, c.RecID FROM Yak AS y INNER JOIN Control AS c ON c.RecID = y.RecID + 1 WHERE y.ItemID = c.ItemID)SELECT [Last Received Date], [Ref Doc#], [Item ID], [Description], Rate, [Value], [Received Qty], [Stock As On Date], AgeFROM ( SELECT ROW_NUMBER() OVER( PARTITION BY pd.ItemID ORDER BY pd.ReceivedDate DESC) AS RowNo, pd.ReceivedDate AS [Last Received Date], pd.PurchaseNumber AS [Ref Doc#], pd.ItemID AS [Item ID], pd.Description, pd.ItemUnitPrice AS Rate, pd.Subtotal AS [Value], pd.ReceivedQty AS [Received Qty], iw.QtyOnHand AS [Stock As On Date], DATEDIFF(DAY, y.ReceivedDate, @Date) AS Age FROM Yak AS y INNER JOIN PurchaseDetail AS pd ON pd.ItemID = y.ItemID INNER JOIN InventoryByWarehouse AS iw ON iw.ItemID = pd.ItemID AND iw.CompanyID = pd.CompanyID AND iw.DivisionID = pd.DivisionID AND iw.DepartmentID = pd.DepartmentID AND iw.QtyOnHand > 0 WHERE pd.CompanyID = @CompanyID AND pd.DivisionID = @DivisionID AND pd.DepartmentID = @DepartmentID AND iw.QtyOnHand > y.rQtyLow AND iw.QtyOnHand <= y.rQtyHigh ) AS dWHERE RowNo = 1ORDER BY [Item ID] E 12°55'05.25"N 56°04'39.16"
|
|
|
|
|
|