Thank you, and come again. (Peso)

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

Thank you, and come again. (Peso)

Author

Topic

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 07:38:22

i have 2 tables

1 table consists of the following data

id price rqty rdate
aaa 10 5 1/2/2008
aaa 100 3 1/3/2008
aaa 12 2 12/3/2008
aaa 77 4 14/4/2008
bbb 9 3 20/4/2008
bbb 7 7 28/4/2008
ccc 1 2 30/4/2008
ccc 3 3 1/5/2008
ccc 2 4 20/5/2008

2 table consists of the following data
bast on last rdate i had generated this 2nd table

id price stock rdate
aaa 10 8 14/4/2008
bbb 77 7 28/4/2008
ccc 100 5 20/5/2008

Now what i have to do is i for 2nd table i have to add 1 more column (age)
iam passing i/p parameter date so that date - fetch the rdate of table1 where stock in table2 <= rqty in table1 = no of days(age)

to calculate age i need to fetch the rdate of table1 where stock in table2 <= rqty in table1(1st we have to compare with last rdate ,if condition fails then compare with previous date )
(while comparing with previous date sum up the last rdate rqty and previous rdate qty) and if sum is more then i have to fetch that particular rdate to caluculate age

so o/p is:
so comparing table2 for ex(id aaa)
so stock of aaa is 8 and whene comparing with table 1 last rdate 14/4/2008 rqty =4
so 8 is not <= 4
so next step comparing with previous rdate 12/3/2008 rqty(2)
we have to sum up bot last rdate rqty n previous rdate rqty(6)
so 8 <=6
again compairing with the previous ldate n summing up(4+2+ 3)
so 8 <=9 so that corresponding date i have to bring out 1/3/2008
for ex i/p date is 1/5/2008
so age = 1/5/2008- 1/3/2008 =60
for bbb=1/5/2008 - 28/4/2008=2
id price stock rdate age
aaa 10 8 14/4/2008 60
bbb 77 7 28/4/2008 2
ccc 100 5 20/5/2008

so like that it follows

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 07:43:04

Are you on SQL Server 2005 or SQL Server 2000?

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 07:44:56

sqlserver 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 08:11:07

[code]-- Prepare sample data
SET DATEFORMAT DMY

DECLARE @History TABLE (ID CHAR(3), Price TINYINT, rQty TINYINT, rDate SMALLDATETIME)

INSERT @History
SELECT 'aaa', 10, 5, '1/2/2008' UNION ALL
SELECT 'aaa', 100, 3, '1/3/2008' UNION ALL
SELECT 'aaa', 12, 2, '12/3/2008' UNION ALL
SELECT 'aaa', 77, 4, '14/4/2008' UNION ALL
SELECT 'bbb', 9, 3, '20/4/2008' UNION ALL
SELECT 'bbb', 7, 7, '28/4/2008' UNION ALL
SELECT 'ccc', 1, 2, '30/4/2008' UNION ALL
SELECT 'ccc', 3, 3, '1/5/2008' UNION ALL
SELECT 'ccc', 2, 4, '20/5/2008'

DECLARE @Products TABLE(ID CHAR(3), Price TINYINT, Stock TINYINT, rDate SMALLDATETIME)

INSERT @Products
SELECT 'aaa', 10, 8, '14/4/2008' UNION ALL
SELECT 'bbb', 77, 7, '28/4/2008' UNION ALL
SELECT 'ccc', 100, 5, '20/5/2008'

-- Stage intermediate results
;WITH Control (ID, rQty, rDate, RecID)
AS (
SELECT ID,
rQty,
rDate,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY rDate DESC)
FROM @History
), Yak (ID, rQtyLow, rQtyHigh, rDate, RecID)
AS (
SELECT ID,
CAST(0 AS TINYINT),
rQty,
rDate,
RecID
FROM Control
WHERE RecID = 1

UNION ALL

SELECT y.ID,
y.rQtyHigh,
y.rQtyHigh + c.rQty,
c.rDate,
c.RecID
FROM Yak AS y
INNER JOIN Control AS c ON c.RecID = y.RecID + 1
WHERE y.ID = c.ID
)

-- Show the expected result
SELECT p.ID,
p.Price,
p.Stock,
DATEDIFF(DAY, y.rDate, p.rDate) AS Age
FROM Yak AS y
INNER JOIN @Products AS p ON p.ID = y.ID
WHERE p.Stock > y.rQtyLow
AND p.Stock <= y.rQtyHigh[/code]

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 10:49:15

this statement: DATEDIFF(DAY, y.rDate, @date) AS Age then showing error as Incorrect syntax near 'DATEDIFF'.this
[iam passing i/p parameter @date(small datetime) so i have to sub that from rdate]

if i comment DATEDIFF(DAY, y.rDate, p.rDate) AS Age this

near ;WITH Control (ID, rQty, rDate, RecID)it is showing error

Types don't match between the anchor and the recursive part in column "rQtyLow" of recursive query "Yak".

Thanx in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 11:07:40

Which version and service pack level do yo uhave on your Microsoft SQL Server 2005 installation?

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 11:21:18

ALTER Procedure [enterprise].[NonMovingItemsLD] --'XYZ','PPP','PWW','22/04/2008'

@CompanyID NVARCHAR(36),

@DivisionID NVARCHAR(36),

@DepartmentID NVARCHAR(36),

@Date SMALLDATETIME

AS

DECLARE @History TABLE(ITEMID NVARCHAR(36), DESCRIPTION NVARCHAR(80), receiveddate SMALLDATETIME, ReceivedQty float)

INSERT @History

SELECT

itemid AS [Item ID],

DESCRIPTION AS [Description],

receiveddate AS [Received Date],

ReceivedQty as [Received Qty]

FROM PURCHASEDETAIL P

WHERE

CompanyID =@CompanyID AND

DivisionID=@DivisionID AND

DepartmentID=@DepartmentID AND

ReceivedQty>0

ORDER BY P.ITEMID

DECLARE @Products TABLE(receiveddate SMALLDATETIME, PURCHASENUMBER NVARCHAR(36), itemid NVARCHAR(36), DESCRIPTION NVARCHAR(80) ,ITEMUNITPRICE MONEY ,SUBTOTAL MONEY,ReceivedQty FLOAT,QtyOnHand FLOAT)

INSERT @Products

SELECT p.receiveddate AS [Last Received Date],

P.PURCHASENUMBER AS [Ref Doc#],

p.itemid AS [Item ID],

P.DESCRIPTION AS [Description],

P.ITEMUNITPRICE as [Rate],

P.SUBTOTAL as [Value],

P.ReceivedQty as [Received Qty],

P.QtyOnHand as [Stock As On Date]

FROM

(SELECT ROW_NUMBER() OVER( PARTITION BY P1.ITEMID Order BY P1.receiveddate DESC) AS RowNo,

P1.receiveddate,

P1.PURCHASENUMBER,

P1.ITEMID,

P1.DESCRIPTION,

P1.ITEMUNITPRICE,

P1.SUBTOTAL,

P1.ReceivedQty,

I.QtyOnHand

FROM PURCHASEDETAIL P1

INNER JOIN InventoryByWarehouse I

ON P1.ItemID=I.ItemID AND

p1.CompanyID=I.CompanyID AND

p1.DivisionID=I.DivisionID AND

p1.DepartmentID=I.DepartmentID AND

P1.CompanyID =@CompanyID AND

p1.DivisionID=@DivisionID AND

p1.DepartmentID=@DepartmentID AND

I.QtyOnHand > 0

)P

WHERE P.RowNo=1

ORDER BY P.ITEMID

;WITH Control (itemid, ReceivedQty, receiveddate,RecID)

AS (

SELECT itemid,

ReceivedQty,

receiveddate,

ROW_NUMBER() OVER (PARTITION BY itemid ORDER BY receiveddate DESC)

FROM @History

),

Yak (itemid, rQtyLow, rQtyHigh, receiveddate,RecID)

AS (

SELECT itemid,

CAST(0 AS TINYINT),

ReceivedQty,

receiveddate,

RecID

FROM Control

WHERE RecID = 1

UNION ALL

SELECT y.itemid,

y.rQtyHigh,

y.rQtyHigh + c.ReceivedQty,

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

p.receiveddate AS [Last Received Date],

P.PURCHASENUMBER AS [Ref Doc#],

p.itemid AS [Item ID],

P.DESCRIPTION AS [Description],

P.ITEMUNITPRICE as [Rate],

P.SUBTOTAL as [Value],

P.ReceivedQty as [Received Qty],

P.QtyOnHand as [Stock As On Date],

y.receiveddate AS Age

DATEDIFF(DAY, y.receiveddate, @Date) AS Age

FROM Yak AS y

INNER JOIN @Products AS p ON p.ItemID = y.ItemID

WHERE p.QtyOnHand > y.rQtyLow

AND p.QtyOnHand <= y.rQtyHigh

AND IAM GETTING ERRORS :

this statement: DATEDIFF(DAY, y.rDate, @date) AS Age then showing error as Incorrect syntax near 'DATEDIFF'.this
[iam passing i/p parameter @date(small datetime) so i have to sub that from rdate]

if i comment DATEDIFF(DAY, y.rDate, p.rDate) AS Age this

near ;WITH Control (ID, rQty, rDate, RecID)it is showing error

Types don't match between the anchor and the recursive part in column "rQtyLow" of recursive query "Yak".

Thanx in advance

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 11:29:52

HI,
ACTUALY IAM A .NET PROGRAMER USING vs 2005 extensions for .NET Framework 2.0 (in built sql server 2005)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-23 : 11:44:30

What a mess!

ALTER PROCEDURE enterprise.NonMovingItemsLD
(
	@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		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],
		pd.QtyOnHand AS [Stock As On Date],
		DATEDIFF(DAY, y.ReceivedDate, pd.ReceivedDate) AS Age
FROM		Yak AS y
INNER JOIN	PurchaseDetail AS pd
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

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-23 : 22:53:06

hi,
thanx for ur reply but........

WHERE pd.CompanyID = @CompanyID(error-Msg 156, Level 15, State 1, Procedure NonMovingItemsLD, Line 62
in this line it is showing -Incorrect syntax near the keyword 'WHERE'.)
AND pd.DivisionID = @DivisionID
AND pd.DepartmentID = @DepartmentID
WHERE iw.QtyOnHand > y.rQtyLow
AND iw.QtyOnHand <= y.rQtyHigh
ORDER BY pd.ItemID

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-24 : 07:24:22

quote:
Originally posted by Anushka

hi,
thanx for ur reply but........

WHERE pd.CompanyID = @CompanyID(error-Msg 156, Level 15, State 1, Procedure NonMovingItemsLD, Line 62
in this line it is showing -Incorrect syntax near the keyword 'WHERE'.)
AND pd.DivisionID = @DivisionID
AND pd.DepartmentID = @DepartmentID
AND iw.QtyOnHand > y.rQtyLow
AND iw.QtyOnHand <= y.rQtyHigh
ORDER BY pd.ItemID

there was an extra WHERE. change it to AND

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 07:30:41

Great!
I obviously missed that one. It's easy when you have no data to test-runt the query against.

Does the query work now?

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-24 : 09:52:06

Hi,
No maaaaaaa it is not comming,showing up the same error near where-Incorrect syntax near the keyword 'WHERE'.)

:(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 10:19:41

Did you do as I and Visakh suggested?
Change the last WHERE to AND.

See post made by me 04/23/2008 : 11:44:30 (This is corrected).

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-24 : 10:24:49

yes i had changed last where to and, then also iam getting error

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 10:30:03

Which error do you get now?
Post full code you use now.

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-24 : 10:33:05

ALTER PROCEDURE enterprise.NonMovingItemsLD
(
@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 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],
pd.QtyOnHand AS [Stock As On Date],
DATEDIFF(DAY, y.ReceivedDate, pd.ReceivedDate) AS Age
FROM Yak AS y
INNER JOIN PurchaseDetail AS pd
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 (line 62)
AND pd.DivisionID = @DivisionID
AND pd.DepartmentID = @DepartmentID
AND iw.QtyOnHand > y.rQtyLow
AND iw.QtyOnHand <= y.rQtyHigh
ORDER BY pd.ItemID

Msg 156, Level 15, State 1, Procedure NonMovingItemsLD, Line 62
Incorrect syntax near the keyword 'WHERE'.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 10:59:15

The error is really on the PurchaseDetail JOIN

CREATE PROCEDURE enterprise.NonMovingItemsLD
(
	@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		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],
		pd.QtyOnHand AS [Stock As On Date],
		DATEDIFF(DAY, y.ReceivedDate, pd.ReceivedDate) 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

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

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-25 : 04:26:41

iam getting all the items ,actually i need only the last received date items among that that item

so iam trying to create temp table and wring row over...but iam getting error

create 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
(
Date SMALLDATETIME,
RefDoc NVARCHAR(36),
ItemID NVARCHAR(36),
Description NVARCHAR(80),
ItemUnitPrice Money,
Subtotal Money,
ReceivedQty float,
QtyOnHand float,
Age int
)

INSERT INTO @tblTemp2

;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 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 * FROM @tblTemp2

Anushka
Yak Posting Veteran

79 Posts

Posted - 2008-04-25 : 04:28:11

quote:
Originally posted by Anushka

iam getting all the items ,actually i need only the last received date items among that itemid

so iam trying to create temp table and using row_number over...but iam getting error

create 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
(
Date SMALLDATETIME,
RefDoc NVARCHAR(36),
ItemID NVARCHAR(36),
Description NVARCHAR(80),
ItemUnitPrice Money,
Subtotal Money,
ReceivedQty float,
QtyOnHand float,
Age int
)

INSERT INTO @tblTemp2

;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 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 * FROM @tblTemp2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-25 : 04:34:51

quote:
Originally posted by Anushka

quote:
Originally posted by Anushka

iam getting all the items ,actually i need only the last received date items among that itemid

so iam trying to create temp table and using row_number over...but iam getting error

create 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
(
Date SMALLDATETIME,
RefDoc 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 * FROM @tblTemp2

Move the insert just before SELECT batch

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources