INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to update the current value based on previous rows value in SQL

How to update the current value based on previous rows value in SQL

How to update the current value based on previous rows value in SQL

(OP)
Hello,

I have a table holding values as below: (also I attached the table picture as well)

rownum Id UserID DecisionTypeDetailDate RushTypeKey ExpectedRushType
1 35428225 System 2014-04-22 22:38:19 8 8
2 35428225 RECALL 2014-04-26 15:05:00 8 8
3 35428225 System 2014-04-26 15:06:57 8 8
4 35428225 AZZOL 2014-04-26 15:48:31 7 7
5 35428225 CHAXY2 2014-04-26 16:02:55 6 6
6 35428225 System 2014-04-28 13:56:13 8 6
7 35428225 System 2014-04-28 13:56:13 8 6
8 35428225 AZZOL 2014-04-28 14:55:53 4 4
9 35428225 TSAPY2 2014-04-28 19:26:45 6 6
10 35428225 System 2016-05-09 21:52:34 8 6
11 35428225 GIYKIJ2 2016-05-11 13:46:24 5 5
12 35428225 HILN2 2016-05-11 14:34:36 5 5
13 35428225 System 2016-05-11 22:45:52 8 5
14 35428225 GIYKIJ2 2016-05-12 14:31:02 6 6
15 35428225 ROBINM2 2016-05-12 17:12:58 7 7


I would like to update the value in RushType column same as ExpectedRushType column.
The rule is if the UserID in ('System','RECALL') then I need to update RushType value same as previous row rushType value which is UserID NOT in ('System','RECALL').

If there is no previous record then keep the same value as is.

NOTE (Table does't have ExpectedRushType colum. I just added to show what I am looking for) Table shows only one ID and ordered by DecisionTypeDetailDate

Any help greatly appreciated.

RE: How to update the current value based on previous rows value in SQL

Although this can probably be done with a single SQL statement, this situation is easiest to do with a CURSOR. Are you familiar with CURSORs in Transact-SQL?

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: How to update the current value based on previous rows value in SQL

(OP)
Hi,
i am not familiar with cursor much. can you please provide an example?
thanks

RE: How to update the current value based on previous rows value in SQL

Too deep to go into here. Here's a link to several examples:
https://www.mssqltips.com/sqlservertip/1599/sql-se...

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


RE: How to update the current value based on previous rows value in SQL

look @ the lead lag function Sqlserver 2012

RE: How to update the current value based on previous rows value in SQL

(OP)
Thanks Johnherman. I been checking cursor example since yesterday and could not find a solution.

If someone can help me specifically to my question above I'll be appreciated.

Thanks

RE: How to update the current value based on previous rows value in SQL

(OP)
Thanka PWise,
I believe the lead, lag functions only goes 1 records before or after. But in my case I need to go more than 1 record.

RE: How to update the current value based on previous rows value in SQL

It's a parameter of lead and lag, how many rows to go forth or back. Besides SUM and other aggregation functions can also go back N rows or unbound preceding. Windowing of records is as felxible as you like it to be, including partioining. So this works with the OVER clause as usual and more.

Bye, Olaf.

RE: How to update the current value based on previous rows value in SQL

Here's a single line query to get you started. It has not been tested.

update table UT set RushTypeKey = ST.RushTypeKey where exists (
select top 1 ST.RushTypeKey from table ST
where ST.UserID not in ('System','RECALL') and ST.rownum < UT.rownum order by ST.rownum desc)

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive agile big data clouds)


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close