asked on Select most recent row and change status of the not so recent rows
Hello Experts!
I need a stored procedure to clean daily information like below:
Order_number CreateDttm stscd
5013951 2014-11-19 06:00:10.917 1
5013951 2014-11-21 06:00:34.570 1
5013951 2014-11-18 06:00:11.830 1
To this instead:
Order_number CreateDttm stscd
5013951 2014-11-19 06:00:10.917 0
5013951 2014-11-21 06:00:34.570 1
5013951 2014-11-18 06:00:11.830 0
So that the order with the latest createdttm is the only valid row (validity being determined by stscd)
Note that there will be other order numbers (multiple active rows )
I need a stored procedure to clean daily information like below:
Order_number CreateDttm stscd
5013951 2014-11-19 06:00:10.917 1
5013951 2014-11-21 06:00:34.570 1
5013951 2014-11-18 06:00:11.830 1
To this instead:
Order_number CreateDttm stscd
5013951 2014-11-19 06:00:10.917 0
5013951 2014-11-21 06:00:34.570 1
5013951 2014-11-18 06:00:11.830 0
So that the order with the latest createdttm is the only valid row (validity being determined by stscd)
Note that there will be other order numbers (multiple active rows )
Microsoft SQL Server
Last Comment
the_b1ackfox
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thank you ScottPletcher & Eghtebas
there are over 20,000 rows in there daily.
Background:
We receive a data feed on a daily basis @ 6am. The data feed is uploaded into a table. (example based upon that table). We just found out that the source will make updates to the data feed (this was never a part of the spec). Whenever they make updates to the data of something that was already entered (now with multiple rows) it causes a duplicate billing charge.
We do not have access to the system uploading the data feed into the database, so I need this SP to clean up the data so only the most recent data is valid. We will have to run this SP after the data has been uploaded. :)
Fox