On 11/16/2015 6:37 PM, Bart Smissaert wrote:
> Now, what I need is for every row the count of preceding rows where
> the ID is the same as the ID of the current row, the date is less than the
> date of the current row and the value is >= 10 and <= 20. If the value is
> outside
> this range then it should stop the count for that current row. If the value
> of the current row is outside this 10 to 20 range than the result is 0 and
> there
> is no need to count preceding rows.
Personally, I'd do it in application code. It can be done in single
pass. If you insist on pure SQL solution, then something like this:
select ID, DATE, VALUE,
(select count(*) from TEST prev
where prev.ID = T.ID and prev.DATE < T.DATE and prev.DATE >
coalesce((select max(DATE) from TEST boundary
where boundary.ID = T.ID and boundary.DATE <= T.DATE
and not(boundary.VALUE between 10 and 20)), '')
) as COUNT_PRECEDING_IN_RANGE
from TEST T;
Performance will likely be, shall we say, less than stellar.
--
Igor Tandetnik
