Skip to content
Advertisement

Finding highest reached threshold

I have a table that contains 2 columns that store values of certain thresholds that can be reached. The table columns are: ThresholdValue (INT), Reached (BIT) and the table looks like this:

ThresholdValue | Reached
------------------------
10000          | 0
20000          | 0 
30000          | 0
45000          | 0
50000          | 0

I need to update the reached column according to the reached threshold. For example, when the reached value is 25000 I want the second row to be set to 1 so it looks like this

ThresholdValue | Reached
------------------------
10000          | 0
20000          | 1
30000          | 0
45000          | 0
50000          | 0

What is the easiest way to solve this problem? Any tips will be strongly appreciated

Advertisement

Answer

You can use a subquery to identify the row:

update t
    set reached = 1
    from (select top (1) t.*
          from t
          where t.ThresholdValue <= 25000
          order by ThresholdValue desc
         ) t;

Another approach would look at the next value:

update t
    set reached = 1
    from (select t.*, lead(ThresholdValue) over (order by ThresholdValue) as next_tv
          from t
         ) t
     where t.ThresholdValue <= 25000 and
           (t.next_tv > 25000 or t.next_tv is null);

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement