Re: [sqr-users] Consecutive Years query

["George Jansen" <GJANSEN@aflcio.org>]

This is Oracle, but the case should be comparable for other systems.

SQL> select * from bogo_event order by event, event_date;

EVENT            EVENT_DATE
---------------- ----------
CANDY            2003-10-31
CANDY            2004-10-31
CANDY            2005-10-31
COAL IN STOCKING 2005-12-25
PARTY            2004-01-01
PARTY            2005-01-01
PARTY            2006-01-01
TIE IN BOX       2002-12-25
TIE IN BOX       2003-12-25
TIE IN BOX       2004-12-25

10 rows selected.

SQL> get /tmp/l3y
  1  SELECT E1.*
  2  FROM BOGO_EVENT E1
  3  WHERE EVENT_DATE >= ADD_MONTHS(SYSDATE, -12)
  4    AND EXISTS
  5        (SELECT 1 FROM BOGO_EVENT E2
  6         WHERE E2.EVENT = E1.EVENT
  7           AND E2.EVENT_DATE = ADD_MONTHS(E1.EVENT_DATE, -12))
  8    AND EXISTS
  9        (SELECT 1 FROM BOGO_EVENT E3
 10         WHERE E3.EVENT = E1.EVENT
 11*          AND E3.EVENT_DATE = ADD_MONTHS(E1.EVENT_DATE, -24))
SQL> /

EVENT            EVENT_DATE
---------------- ----------
PARTY            2006-01-01
CANDY            2005-10-31



>>> Brooke.Funk@firstfedbankkc.com 06/26/06 5:02 PM >>>
I am trying to figure out how to write a query looking for data in a particular 
table that has happened on the same month and day but different year for the 
last 3 years.  Does anyone have any idea how I would go about writing something 
like this?  



NOTICE:  This e-mail is intended solely for the use of the
individual to whom it is addressed and may contain information
that is privileged, confidential or otherwise exempt from
disclosure.  If the reader of this e-mail is not the intended
recipient or the employee or agent responsible for delivering
the message to the intended recipient, you are hereby notified
that any dissemination, distribution, or copying of this
communication is strictly prohibited.  If you have received this
communication in error, please immediately notify us by replying
to the original message at the listed e-mail address.

Thank you.


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org 
http://www.sqrug.org/mailman/listinfo/sqr-users


_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users