Solved

Updating row with most current date

Posted on 2011-03-09
12
42 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change variables in SQL table 6 87
SQL Restore Script - Syntax Error 8 96
sql query help 2 51
Why i am getting a star, SSMS does not show me any error. Division Error 5 20
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
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…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

785 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