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 )

SOLUTION

Mike Eghtebas

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

the_b1ackfox

ASKER

Eghtebas, can you make it so the SP runs without having to put the order number in it?

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

ASKER CERTIFIED SOLUTION

Scott Pletcher