Solved

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

Posted on 2014-12-08
4
79 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
  • 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error to start debug MS SQL - Help 8 84
t-sql, insert data in table normally, but read from view 3 30
SQL Server Resume 5 44
SQL Instance service gone? 5 33
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

685 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