CODEWITHSUNDEEP
sqlsql-servert-sql
Neil Ak
Neil Ak

Reputation: 21

SQL - Select rows after reaching minimum value/threshold

Using Sql Server Mgmt Studio. My data set is as below. 

ID   Days   Value   Threshold
A    1      10      30
A    2      20      30
A    3      34      30
A    4      25      30
A    5      20      30
B    1      5       15
B    2      10      15
B    3      12      15
B    4      17      15
B    5      20      15

I want to run a query so only rows after the threshold has been reached are selected for each ID. Also, I want to create a new days column starting at 1 from where the rows are selected. The expected output for the above dataset will look like

ID   Days   Value   Threshold   NewDayColumn
A    3      34      30              1
A    4      25      30              2
A    5      20      30              3
B    4      17      15              1
B    5      20      15              2

It doesn't matter if the data goes below the threshold for the latter rows, I want to take the first row when threshold is crossed as 1 and continue counting rows for the ID. Thank you!

Upvotes: 2

Views: 1726

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can use window functions for this. Here is one method:

select t.*, row_number() over (partition by id order by days) as newDayColumn
from (select t.*,
             min(case when value > threshold then days end) over (partition by id) as threshold_days
      from t
     ) t
where days >= threshold_days;

Upvotes: 1

Related Questions