sql - Update Table with different values depending on a column

Question

I have a table:

ProductCode         Date                       Number
COD666AN            2020-12-18 12:02:47.330    5
COD666AN            2020-12-18 12:02:47.770    6
COD666AN            2020-12-18 12:18:05.587    7
COD666AN            2020-12-18 15:13:08.993    8
COD666AN            2020-12-18 15:13:09.400    9
COD666AN            2020-12-18 15:31:39.497    20000000
COD666AN            2020-12-23 11:12:06.140    10
COD666AN            2020-12-23 11:14:06.720    11
COD666AN            2020-12-23 11:14:52.957    12
COD666AN            2020-12-23 11:14:53.360    13
COD666AN            2020-12-23 11:16:49.673    30000000

And i was trying to update the column "Number" to have a result like this:

ProductCode         Date                       Number
COD666AN            2020-12-18 12:02:47.330    20000000
COD666AN            2020-12-18 12:02:47.770    20000000
COD666AN            2020-12-18 12:18:05.587    20000000
COD666AN            2020-12-18 15:13:08.993    20000000
COD666AN            2020-12-18 15:13:09.400    20000000
COD666AN            2020-12-18 15:31:39.497    20000000
COD666AN            2020-12-23 11:12:06.140    30000000
COD666AN            2020-12-23 11:14:06.720    30000000
COD666AN            2020-12-23 11:14:52.957    30000000
COD666AN            2020-12-23 11:14:53.360    30000000
COD666AN            2020-12-23 11:16:49.673    30000000

I would like to avoid the cursors, but if there is no way to avoid them doesn't matter.

Note: My table could have more "blocks" ( i call blocks the dates between the higher numbers), so for example.... 14, 15, 16, 17 , 40000000.....

Logic: As you can see the table is ordered by the column [Date] asc, so i would like to update the column [Number] with the same value of the max value of the column [Number] before the next higher value, in order to have all the values before 20000000 equal to 20000000 and before 30000000 but not before 20000000 equal to 30000000.

Answer 1

I can only speculate on the logic. It looks like you want a cumulative (in reverse order) min, where number is above a threshold:

select t.*,
       min(case when number > 10000 then number end) over (partition by productcode order by date desc) as imputed_number
from t;

You can then use this in an update:

with toupdate as (
      select t.*,
             min(case when number > 10000 then number end) over (partition by productcode order by date desc) as imputed_number
      from t
     )
update toupdate
     set number = imputed_number
     where number <> imputed_number;

Here is a db<>fiddle.