Solved

Updating row with most current date

Posted on 2011-03-09
12
36 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:guinnie
Comment Utility
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
Comment Utility
LowFatSpread or Sharath:


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

Expert Comment

by:Sharath
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
you meant Lfs?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

771 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

11 Experts available now in Live!

Get 1:1 Help Now