Solved

Updating row with most current date

Posted on 2011-03-09
12
39 Views
Last Modified: 2015-04-08
I have a table with a customer numbe, order numberr , 'current indicator' and a 'create date'.
There will be multiple rows with same customer number, current indicator'  and order number.
The only difference will be create date.
I want to update current_indicator with a 'Y' on row with most current date.
Can someone do this using max function on the date column ?

0
Comment
Question by:guinnie
  • 5
  • 5
  • 2
12 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35086826
update U
 set current_indicator='Y'

          from (select *
                             ,row_number() over (partition by customer_number,order_number order by create_date desc) as rn
                      from yourtable) as U
            where rn=1
              and current_indicator <>'Y'
0
 

Author Comment

by:guinnie
ID: 35086995
That didn't work. No rows returned.
Ok , i tried to simplify request but I'll give you more info.
Here is my data. First column is student_skey. Second column is create_date.

127      2010-09-25 04:08:14.000
127      2010-09-25 06:19:45.000
127      2010-10-11 23:28:29.000
127      2010-10-12 02:18:30.000
127      2010-10-16 19:26:50.000

Here is my code based on your response:

update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator <> 'N'
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35088428
try this.
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from @table) as U
 where rn=1
       and ISNULL(current_indicator,'N') <> 'Y'

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35088755
you want to change rows where the current indicator isn't 'Y' to 'Y'

if the rows currently contain an 'N' then you want this.....

update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by create_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'


0
 

Author Comment

by:guinnie
ID: 35094012
The point is I only want to update the row with the most current date.

Not all rows.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35094751
both my and sharaths solution shoulld only be updating the student row with the latest date per student...

127      2010-09-25 04:08:14.000
127      2010-09-25 06:19:45.000
127      2010-10-11 23:28:29.000
127      2010-10-12 02:18:30.000
127      2010-10-16 19:26:50.000

so we should only affect the 2010-10-16 row for student 127,,,,

what are we missing can you provide an example with multiple students/dates and highklight what you desire to be updated...

(include the current indicator , since i'm getting confused as to what its state ie/should be.....)
0
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.

 

Author Comment

by:guinnie
ID: 35094984
When I run the query below , the result says '0 rows affected'.

I need the current indicator set to 'Y' on the most current row.

Herre is the sql is used:
go
begin transaction
go
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'
       and student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'
go
0
 

Author Comment

by:guinnie
ID: 35096197
LowFatSpread or Sharath:


Can you see anything in SQL that was not correct ?
0
 
LVL 40

Expert Comment

by:Sharath
ID: 35096922
Can you check the current_indecator value with a SELECT query for that record?
select * 
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment) as U
 where rn=1
       and current_indicator = 'N'
       and student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'

Open in new window

0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 35096980
yes you need the school_year check to be "inside" the scope of the window function...

either as part of the partition clause or in the where clause...

try this...
begin transaction
go
update  U
 set current_indicator='Y'
  from (select *, row_number() over
           (partition by student_skey order by cre_date desc) as rn
                 from fa_school_enrollment
             where   student_skey = '127'
                     and  SCHOOL_YEAR_code = '20102011'
          ) as U
 where rn=1
       and current_indicator = 'N'
      
go

Open in new window

0
 

Author Comment

by:guinnie
ID: 35097129
Sharath:

Thanks for the quick reply.
I took the filters and put them in the 'partition' statement
and reran update. Then it worked perfectly. See below.
Thanks for working with me on this.

update U
 set current_indicator='Y'
          from (select *
                             ,row_number() over (partition by student_skey order by cre_date desc) as rn
                      from fa_school_enrollment
                      where student_skey = '127'
                      and  SCHOOL_YEAR_code = '20102011') as U
            where rn=1
             
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35097978
you meant Lfs?
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

911 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

21 Experts available now in Live!

Get 1:1 Help Now