CODEWITHSUNDEEP
sqlsybase
Zanam
Zanam

Reputation: 4807

Gathering consecutive information from SQL

I am using sybase.

I have a table with columns named time and quantity. Quantity can be either zero or non-zero. I need to find all the cases where for any time any subsequent quantity within 1 sec is > 0 and original quantity is greater than 40.

I am not able to think this problem in terms of SQL structure but if it were a C++ code I would have easily done it using a for loop, etc.

Let me try to explain this with an example.

From the attached image where I have gathered the data in ascending time:

RowNumber   Time        Quantity

1        10:01:01.000     100

2        10:01.01.001     50

3        10:01:01.002      0

4        10:01.01.003    100

5        10:01:03.001    100

Upvotes: 1

Views: 116

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Assuming by "next" you really mean next and there are not multiple records, then you can do this with lead.

select t.RowNumber, t.Time, t.Quantity
from (select t.*,
             lead(time, 1) over (order by time) as nextTime,
             lead(quantity, 1) over (order by time) as nextQuantity
      from t
     ) t
where datediff(ms, t.time. t.nexttime) <= 1000 and
      (t.Quantity > 40 and t.nextQuantity > 0)

If you do not have the lead() function, you can do this as follows:

select t.RowNumber, t.Time, t.Quantity
from (select t.*,
             (select min(time) from t t2 where t2.time > t.time) as nexttime
      from t
     ) left outer join
     t tnext
     on tnext.time = t.nexttime
where datediff(ms, t.time. tnext.time) <= 1000 and
      (t.Quantity > 40 and tnext.Quantity > 0)

Upvotes: 4

Related Questions