I have a table holding transaction dates and counter like this

I have to find counter 12 and counter 120. The counter is an incrementing counter, and it becomes 1 again at 250.
12 => counter incrementing but downs to 12 and become 89 again after 12
120 => counter jumps to 120 and become 93 after 120

How I can write to SQL Query for this. (Oracle 11)

CodePudding user response：

You can use LEAD and LAG for that. If I understand correct, your counter is valid when it's between the previous and the next counter. This can be verified like that:

WITH orderedData AS
(SELECT no_card, yourdate, counter,
LAG(counter, 1, 0) OVER (ORDER BY yourdate) AS counter_prev,
LEAD(counter, 1, 0) OVER (ORDER BY yourdate) AS counter_next
FROM yourtable)
SELECT counter FROM orderedData 
WHERE NOT counter BETWEEN counter_prev AND counter_next
AND counter_prev < counter_next;

This will return 12 and 120 for your sample data since those two values don't satisfy this condition.

Note: It might be needed to add a PARTITION BY card_no and/or date clause to both the LAG and LEAD part if the counter is separated per card number and/or date. That's not clear from your sample data.

So feel free to extend this query if necessary.

CodePudding user response：

The rows

WHERE counter NOT BETWEEN 
        CASE WHEN 250 = LAG(counter) OVER(PARTITION BY no_card ORDER BY dat) THEN 0 
        ELSE 
        LAG(counter) OVER(PARTITION BY no_card ORDER BY dat) END 
    AND LEAD(counter) OVER(PARTITION BY no_card ORDER BY dat)