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_b1ackfoxCIOAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase 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_b1ackfoxCIOAuthor 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
Scott PletcherSenior 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
the_b1ackfoxCIOAuthor Commented:
Thank you ScottPletcher & Eghtebas
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.