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

[burakcan]

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.

 

[johnherman]

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)

 

[burakcan]

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

 

[johnherman]

Too deep to go into here. Here's a link to several examples:

https://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/

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

 

[PWise]

look @ the lead lag function Sqlserver 2012

 

[burakcan]

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

 

[burakcan]

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.

 

[Olaf Doschke]

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.

 

[johnherman]

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)