CODEWITHSUNDEEP
sqlsql-servert-sql
Pigouvian
Pigouvian

Reputation: 13

T-SQL to find length of time a particular value is in-range

I have a table in SQL Server where, for each row r at time t, I would like to find the first t + i for some function of r where abs(f(r, t + i) - f(r, t)) > epsilon.

I can imagine doing this with two cursors, but this seems highly inefficient.

Any of the T-SQL gurus out there have any advice?

Upvotes: 1

Views: 126

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I'm not a big fan of correlated subqueries. But, it seems useful in this case. The following code returns the minimum "sequence number" of the first row after the given row subject to your condition:

with t as (
    select t.*, f(r, t) as fval, row_number() over (order by <ordering>) as seqnum
    from table t
)
select t.*,
       (select min(t2.seqnum)
        from t t2
        where t2.seqnum > t.seqnum and
              abs(t2.fval - t.fval) > <epsilon>
       ) as next_seqnum
from t

To make this work, you need to specify <ordering> and <epsilon>. is how you know the order of the rows (t would be a good guess, if I had to guess).

Upvotes: 1

RichardTheKiwi
RichardTheKiwi

Reputation: 107686

select a.t, b.t  --- and what other columns you need
from tbl a -- implicitly each row of table
cross apply (
    select top(1) * -- the first, going upwards along b.t
    from tbl b
    where a.t < b.t -- look for records with later t than the source row
      and <here's your function between a row and b row>
    order by b.t asc
) x

Upvotes: 1

Related Questions