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 )
LVL 9
the_b1ackfoxAsked:
Who is Participating?
 
Scott PletcherConnect With a Mentor Senior DBACommented:
UPDATE tn
SET stscd = 0
FROM table_name tn
WHERE
    EXISTS (
        SELECT 1
        FROM table_name tn2
        WHERE
            tn2.Order_number = tn.Order_number AND
            tn2.CreateDttm > tn.CreateDttm
    )

You'll want an index on Order_number that includes CreateDttm for this (such as the clustering index) to run really well, but I'm hoping you already have one :-).
0
 
Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented:
Create Proc spKeepRecentOrderValidFor
@Order_number int
As
Begin

Update Table1 Set stscd=0 Where Order_number=@Order_number;
Update Table1 Set stscd =1 Where Order_number=@Order_number And stscd in (Select top 1 stscd Order By CreateDttm Desc);                        

End

To test:
Exec spKeepRecentOrderValidFor 5013951
0
 
the_b1ackfoxAuthor Commented:
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
0
 
the_b1ackfoxAuthor Commented:
Thank you ScottPletcher & Eghtebas
0
All Courses

From novice to tech pro — start learning today.