update current row with Previous row Valid value

Database
Enthusiast

update current row with Previous row Valid value

I have a table as similar to below and

1. I would need to update the CASE_ IN when there is a null then I must update it with previous non-null value.

Will this below SQL work?

last_value(case_in ignore nulls) over (partition by ID order by date rows unbounded preceding)

2. Next, I need to update the BR_CD. any values between 0-100  and NULL are invalid hence those needs to be updated to last previous BR_CD. How do I achieve this, please can you help? I have given the sample input and output.

 

IDCASE_INBR_CDDate
1Y12341/2/12
1Y101/2/12
1 201/3/12
1 301/4/12
1Y102/8/12
2Y56783/3/18
2Y?3/5/18
2Y87653/7/18

 

Expected Output

 

IDCASE_INBR_CDDate
1Y12341/2/12
1Y12341/2/12
1Y12341/3/12
1Y12341/4/12
1Y12342/8/12
2Y56783/3/18
2Y56783/5/18
2Y87653/7/18

 


Accepted Solutions
Junior Contributor

Re: update current row with Previous row Valid value

Of course, should be br_cd :

last_value(case when br_cd not between 0 and 100 then br_cd end ignore nulls) 
over (partition by ID order by date rows unbounded preceding)

 

1 ACCEPTED SOLUTION
6 REPLIES 6
Highlighted
Junior Contributor

Re: update current row with Previous row Valid value

The LAST_VALUE should return what you want and for the 2nd case you simply modify it to:

last_value(case when case_in not between 0 and 100 then case_in end ignore nulls) 
over (partition by ID order by date rows unbounded preceding)

 

Enthusiast

Re: update current row with Previous row Valid value

should it be br_cd? because I need to update the br_Cd based on case_in

last_value(case when case_in not between 0 and 100 then br_cd end ignore nulls) 
over (partition by ID order by date rows unbounded preceding)

 

Junior Contributor

Re: update current row with Previous row Valid value

Of course, should be br_cd :

last_value(case when br_cd not between 0 and 100 then br_cd end ignore nulls) 
over (partition by ID order by date rows unbounded preceding)

 

Enthusiast

Re: update current row with Previous row Valid value

Dieter thank you so much! But I need to update the br_cd based on the case_in
Junior Contributor

Re: update current row with Previous row Valid value

Your expected output calculates both columns independently...

Enthusiast

Re: update current row with Previous row Valid value

Thank you dieter! you are amazing. The solution worked