Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 thanx come again visakh n peso..

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
)
AS
SET NOCOUNT ON
DECLARE @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 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 = 1

UNION 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
)

INSERT INTO @tblTemp2
SELECT 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
ORDER BY pd.ItemID


SELECT 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],
Age
FROM
(SELECT ROW_NUMBER() OVER( PARTITION BY ITEMID Order BY receiveddate DESC) AS RowNo,
receiveddate,
PURCHASENUMBER,
ITEMID,
DESCRIPTION,
ITEMUNITPRICE,
SUBTOTAL,
ReceivedQty,
QtyOnHand,
Age
FROM @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
)
AS

SET 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 = 1

UNION 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],
Age
FROM (
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 d
WHERE RowNo = 1
ORDER BY [Item ID][/code]

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-25 : 05:27:44
Msg 156, Level 15, State 1, Procedure NonMovingItemsList, Line 79
Incorrect syntax near the keyword 'WHERE'.

near Last but 1 line error - WHERE RowNo = 1


Go to Top of Page

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-25 : 08:12:41
Thank u vvvvvvvvvvv much.........................

both of u...
Go to Top of Page

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"
Go to Top of Page

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
)
AS

SET 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 = 1

UNION 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],
Age
FROM (
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 d
WHERE RowNo = 1
ORDER BY [Item ID]


E 12°55'05.25"
N 56°04'39.16"


Go to Top of Page
   

- Advertisement -