March 6, 2012 at 1:39 pm
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,
Skybvi
Regards
Sushant Kumar
MCTS,MCP
March 6, 2012 at 1:52 pm
You know with over 1,100 points you should know how to post a question. ddl, sample data, desired output.
Take a look at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 6, 2012 at 6:53 pm
No, no cursor necessary, but as Sean pointed out we need the DDL for the table, sample data for the table, expected results when all is done. It would also be nice to see what you have done so far to solve your problem.
Be sure to read the article Sean suggested. You will also find a link to it in my signature block as well.
March 7, 2012 at 7:28 am
I dont think DDL is needed and sample data i already put..
Leave the question if u can't help.
Regards
Sushant Kumar
MCTS,MCP
March 7, 2012 at 7:41 am
SKYBVI (3/7/2012)
I dont think DDL is needed and sample data i already put..Leave the question if u can't help.
It's not that we can't help. It's that you can't help us to help you. This does not need to be done with a cursor but I am not going to write up some pseudocode which would require me to write ddl for your problem to make sure that I was posted was not incorrect.
The point here is to make it easy for people to help you. That means give them something to work with. You are expecting people to help you with code but we don't know datatypes, table names etc...and unless you are paying me I am NOT going to waste my time helping you put together the setup so I can work on your issue.
How do you expect somebody to come up with anything resembling decent code when you didn't give enough details in the first place?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 7, 2012 at 7:42 am
SKYBVI (3/7/2012)
I dont think DDL is needed and sample data i already put..Leave the question if u can't help.
Everyone that has posted in this thread could help. But you seem unwilling to accept it.
If I paste the "sample data" you have posted into management studio, what happens?
Msg 102, Level 15, State 1, Line 3
Incorrect syntax
Instead, post your sample data in a readily consumable format, e.g.
CREATE TABLE yourTable (itemno CHAR(4),itemID INT, newprice MONEY, oldprice MONEY, changedate DATETIME, seqid TINYINT)
INSERT INTO yourTable
SELECT 'a123', 12345, 5.50, 2.30, '2012-03-06', 1
UNION ALL SELECT 'a123', 12345, 5.25, 2.65, '2012-01-09', 2
March 7, 2012 at 7:45 am
SKYBVI (3/7/2012)
I dont think DDL is needed and sample data i already put..Leave the question if u can't help.
This response is unprofessional. If you want our help, you need to help us. There are people out here willing to help you but we aren't going to take the time to figure out you table definitions, write up dml to populate those tables and then work on solving your problem when 1) we are volunteers offer to help on our own time and 2) there are others out there willing to provide us with the information we need to help them.
March 7, 2012 at 7:55 am
SKYBVI (3/6/2012)
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,
Skybvi
No problem. Simply build a CTE with ROW_NUMBER() OVER and a MIN comparison for dupes sorted in descending order with an outer update of row number 1 with row number 2 and you're done.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply