Solved

Select most recent row and change status of the not so recent rows

Posted on 2014-12-08
4
84 Views
Last Modified: 2014-12-08
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 )
0
Comment
Question by:the_b1ackfox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 34

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 100 total points
ID: 40487603
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
 
LVL 9

Author Comment

by:the_b1ackfox
ID: 40487671
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
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 40487677
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
 
LVL 9

Author Closing Comment

by:the_b1ackfox
ID: 40487713
Thank you ScottPletcher & Eghtebas
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

632 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question