Solved

Updating row with most current date

Posted on 2011-03-09
12
47 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
[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
  • 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 41

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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
 

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 41

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

623 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