Update with cursor - 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 2005 Forums

Transact-SQL (2005)

Update with cursor

Author

Topic

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-06 : 15:39:03

Hi,
I have a table which has itemno,itemID,newprice,oldprice,changedate,seqid

the data is like this :-
a123 12345 5.50 2.30 2012-03-06 1
a123 12345 5.25 2.65 2012-01-09 2
a123 12345 5.30 2.88 2011-11-13 3

a127 67890 2.1 1.4 2012-03-05 1
a127 67890 2.4 1.7 2012-01-29 2
a127 67890 2.18 1.8 2011-09-12 3

There are 7-8 itemno with 3 rows of data each.
I want to update the latest itemno with new price and old price.

For ex
desired output :--

a123 12345 5.50 5.25 2012-03-06 1
a127 67890 2.1 2.4 2012-03-05 1

If you note, I just want updating the (seqid =1 for each itemnmbr)
with the oldprice =latest newprice(seqid=2),

BUT only one thing I want to check that if oldprice =latest newprice, then it should take the value from seqid =3( that is more old (newprice)

So it would be like a cursor, it would go until old price <> latest newprice.

Regards,
Sushant
DBA
Virgin Islands(U.K)

rocknpop
Posting Yak Master

201 Posts

Posted - 2012-03-07 : 00:18:05

Will this work:

--YOUR DATA
DECLARE @TEMP TABLE
(itemno VARCHAR(20),itemID INT,newprice DECIMAL(18,2),oldprice DECIMAL(18,2),changedate SMALLDATETIME,seqid INT)
DECLARE @oldprice DECIMAL(18,2)
DECLARE @newprice DECIMAL(18,2)
DECLARE @prevoldprice DECIMAL(18,2)

DECLARE @TEMP2 TABLE
(itemno VARCHAR(20),itemID INT,newprice DECIMAL(18,2),oldprice DECIMAL(18,2),changedate SMALLDATETIME,seqid INT)

INSERT INTO @TEMP
SELECT 'a123', 12345, 5.50, 2.30,'2012-03-06', 1
UNION
SELECT 'a123', 12345, 2.30, 2.65,'2012-01-09', 2
UNION
SELECT 'a123', 12345, 5.30, 2.88,'2011-11-13', 3
UNION
SELECT 'a127', 67890, 2.1, 1.4,'2012-03-05', 1
UNION
SELECT 'a127', 67890, 2.4, 1.7, '2012-01-29', 2
UNION
SELECT 'a127', 67890, 2.18, 1.8,'2011-09-12', 3

INSERT INTO @TEMP2
SELECT * FROM @TEMP
ORDER BY itemID, seqid DESC

SET @oldprice = 0
SET @newprice = 0

UPDATE @TEMP2 SET
oldprice = @oldprice,
@oldprice = CASE WHEN seqid <> 1 THEN oldprice
ELSE
CASE WHEN seqid = 1 AND oldprice <> @newprice THEN @newprice ELSE @prevoldprice END
END,
@newprice = newprice, @prevoldprice = CASE WHEN seqid = 3 THEN oldprice ELSE @prevoldprice end

SELECT * FROM @TEMP2

--------------------
Rock n Roll with SQL

skybvi
Posting Yak Master

193 Posts

Posted - 2012-03-07 : 08:58:01

Hi,
T hanks btu the a123 value u r getting wrong, its not hte same value wich i mentioned in the desired output.

Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2012-03-07 : 11:16:12

quote:
Originally posted by skybvi

Hi,
T hanks btu the a123 value u r getting wrong, its not hte same value wich i mentioned in the desired output.

Thanks.

Regards,
Sushant
DBA
Virgin Islands(U.K)

Maybe it was a typo. :)

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

rocknpop
Posting Yak Master

201 Posts

Posted - 2012-03-07 : 23:50:51

Replace the 2nd row of @TEMP insert to:
SELECT 'a123', 12345, 5.25, 2.65,'2012-01-09', 2

I just changed the newprice from your 5.25 to 2.30. If you understand the complete logic then you can simply put any value and it will give the proper output. Put this value of 5.25 instead of 2.30 for a123 and check the result. But yes, sorry it's not exactly the same value as you mentioned.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources