No Cursors Please - - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

No Cursors Please -

Author

Topic

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-02-14 : 11:06:48

Hello :

I'm trying to get the next quantity of the part in the Below table.
Parts have id's that increment and reset on change in part number.
The Next _qty filed for the last record per part should always be null.
Any suggestions is appreciated.
DDL below after example.

P.S. Like I said I have done this with cursors but am struggling for a solution without using them.

Thank you.

INPUT

ID _____ PART _____ QTY _____ NEXT_QTY _____
1 _____ AAA _____ 10 _____ NULL _____
2 _____ AAA _____ 12 _____ NULL _____
3 _____ AAA _____ 02 _____ NULL _____
1 _____ BBB _____ 10 _____ NULL _____
2 _____ BBB _____ 12 _____ NULL _____
1 _____ CCC _____ 02 _____ NULL _____

OUTPUT

ID _____ PART _____ QTY _____ NEXT_QTY _____
1 _____ AAA _____ 10 _____ 12 _____
2 _____ AAA _____ 12 _____ 02 _____
3 _____ AAA _____ 02 _____ NULL _____
1 _____ BBB _____ 10 _____ 12 _____
2 _____ BBB _____ 12 _____ NULL _____
1 _____ CCC _____ 02 _____ NULL _____

DDL

CREATE TABLE [dbo].[TEST1] (
[ID] [int] NULL,
[PART] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[QTY] [int] NULL ,
[NEXT_QTY] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (1,'AAA',10,NULL)
INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (2,'AAA',12,NULL)
INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (3,'AAA',2,NULL)
INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (1,'BBB',10,NULL)
INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (2,'BBB',12,NULL)
INSERT INTO [TEST1]([ID],[PART], [QTY], [NEXT_QTY])
VALUES (1,'CCC',2,NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-14 : 11:19:44

[code]UPDATE t1
SET t1.NEXT_QTY=t2.QTY
FROM Table t1
INNER JOIN Table t2
ON t2.PART=t1.PART
AND t2.ID=t1.ID + 1[/code]

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-02-14 : 11:20:22

Someone is probably going to jump in a ruin this beautiful learning experience by writing the code for you ...

Nevertheless ....

Write out (in prose) what the next_qty equals ... "The NEXT_QTY equals the QTY from the row in TEST1 that has the same PART and the ID is ...."

Sit there an look at the sentence you just wrote and see if you can translate that into select query. Then see how you can work that select query into an update statement.

You can do this ...

Jay
to here knows when

EDIT: Damn it ... another opportunity lost ...

dpais
Yak Posting Veteran

60 Posts

Posted - 2008-02-14 : 11:47:30

thank you both for your quick replies -

Page 47 ... That is exactly how i think of solutuions -

H?eres what i came up with ... but it's leaving out the last record -

select a.[id], a.[part], a.[qty], b.[qty] as next_qty
from test1 a, test1 b
where a.[part] = b.[part]
and
a.[id] = b.[id] -1

<< WHAT AM I DOING WRONG !! >>

-- p.s. dont worry about the update ... that part will be easy once the logic is figured out !!

thanks again.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-02-14 : 11:48:41

Hint: what you've got there works great for PREV_QTY ...

Jay
to here knows when

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-14 : 12:59:07

My head is going to explode!
Too much information

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources