CODEWITHSUNDEEP
sqlms-access
Pandora Midnight
Pandora Midnight

Reputation: 11

How to find a specific value in consecutive date

I need your help for a little issue.

I use MS ACCESS to work with a database and I need to resolve a query. My query asks: Find the CUSTOMER_ID and TRANSC_ID where 2 consecutive value between 200 and 500 WITHIN the same transc_id.

I explain.

I have this table in this format:

CUSTOMER_ID    TRANSC_ID    VALUE   VALUE_DATE
51             10           15      29-12-1999
51             10           20      15-07-2000
51             10           35      18-08-2000
51             10           250     30-08-2000
51             10           13      10-09-2000
51             10           450     15-09-2000
51             11           5       15-09-2000
51             11           23      30-09-2000
51             11           490     10-10-2000
51             11           300     12-10-2000
51             11           85      30-10-2000
51             11           98      01-01-2000
53             10           65      15-10-2000
53             10           14      29-12-2000

And I need just

51             11           490     10-10-2000
51             11           300     12-10-2000

because the two values is consecutive (and both of them is >250 and <500).

How can I make a query in MS ACCESS to obtain this result?

Thank you.

Upvotes: 1

Views: 31

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You can get the "next" and "previous" values using correlated subqueries, and then do the comparison:

select t.*
from t
where t.value between 200 and 500 and
      ( (select top 1 t2.value
         from t as t2
         where t2.CUSTOMER_ID = t.CUSTOMER_ID and t2.TRANSC_ID = t.TRANSC_ID and
               t2.value_date > t.value_date
         order by t2.value_date
        ) between 200 and 500 or
        (select top 1 t2.value
         from t as t2
         where t2.CUSTOMER_ID = t.CUSTOMER_ID and t2.TRANSC_ID = t.TRANSC_ID and
               t2.value_date < t.value_date
         order by t2.value_date desc
        ) between 200 and 500
       );

Upvotes: 3

Related Questions