Re: SQL Query To Establish Consecutive "Years"

this isn't perfect, but it might give you a start:

select p1.org_id, p1.req_id, p1.year, p2.req_id,

    p2.year, p3.req_id, p3.year
from
(select r.req_id, r.org_id, year
from req r, pay p
where r.req_id = p.req_id) p1,
(select r.req_id, r.org_id, year
from req r, pay p
where r.req_id = p.req_id) p2,
(select r.req_id, r.org_id, year
from req r, pay p
where r.req_id = p.req_id) p3
where p1.year = p2.year -1 and p2.year = p3.year -1

      and p1.org_id = p2.org_id and p2.org_id = p3.org_id

there are probably other more clever ways to do it, too. also not that it gets all consecutive 3 year periods within any period that happens to be more than 3 years consecutive. i.e., 1999-2000-2001-2002 will show up as two different 3 year periods (99-01, 00-02).

ctbattles_at_hotmail.com (Chris Battles) wrote in message news:<7b78a601.0211151655.7b7e1c68_at_posting.google.com>...
> I'm trying to construct a query that will find activity for a
> particular organization that has taken place in three consecutive
> "years" in the row (in this case the year is just a number and not a
> true date data type.) For example, to be valid we should find payments
> in 2002, 2001, and 2000.
>
> You usually join each of the tables by their primary keys as follows:
>
> SELECT name, req_amt, budgyear, pay_amt
> FROM Organization O, Request R, Payment P
> WHERE O.org_id = R.org_id AND
> R.req_id = P.req_id
> ORDER BY name, budgyer DESC;
>
> Each organization can have multiple requests and each request can have
> multiple payments.
>
> Here are the tables:
>
> Organization:
> -------------------------------
> ORG_ID NUMBER(10) NOT NULL (PRIMARY_KEY)
> NAME VARCHAR2(100)
>
> Request:
> -------------------------------
> REQ_ID NUMBER(10) NOT NULL (PRIMARY_KEY)
> ORG_ID NUMBER(10) NOT NULL (FOREIGN_KEY)
> REQ_AMT NUMBER(13,2)
>
> Payment:
> -------------------------------
> PAY_ID NUMBER(10) NOT NULL (PRIMARY_KEY)
> REQ_ID NUMBER(10) NOT NULL (FOREIGN_KEY)
> PAY_AMT NUMBER(13,2)
> BUDGYEAR NUMBER(4)
>
> Any help would be much appreciated. Thanks!
Received on Tue Nov 19 2002 - 12:39:46 CST