Solved

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

Posted on 2014-12-08
4
77 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 33

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:
ScottPletcher 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
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
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

912 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now