RE: [sqr-users] Consecutive Years query

["Bob Stone" <bstone@fastenal.com>]

Assuming that your table has a 'action_date' meaning that whatever
'happened' is documented in a date field, and assuming you have Oracle
(because that's what I'm about to write this SQL in), and assuming you
want it to happen 3 times, and assuming it has to be 'consecutive'
(meaning if it happened in 03, 05, 06 we don't want to return it).  Call
your table (or view if it's more complicated SQL) tab1.



BEGIN-SELECT
some_fields


FROM tab1 a, tab1 b, tab1 c
WHERE (fill in filter clause for this event on a)
  (join on whatever common keys/fields for this event a to b)
  (join on whatever common keys/fields for this event a to c)
  AND to_char(a.action_date, 'DD') =  to_char(b.action_date, 'DD')
  AND to_char(a.action_date, 'MM') =  to_char(b.action_date, 'MM')
  AND to_number(to_char(a.action_date,'YYYY')) =
to_number(to_char(b.action_date,'YYYY')) - 1
  AND to_char(a.action_date, 'DD') =  to_char(c.action_date, 'DD')
  AND to_char(a.action_date, 'MM') =  to_char(c.action_date, 'MM')
  AND to_number(to_char(a.action_date,'YYYY')) =
to_number(to_char(c.action_date,'YYYY')) - 2
END-SELECT


the day and month fields of A, B, C will all match, the year parts of
the date will be in order A, B, C.

This is not the most sophisticated way to look for it - but once you
find a few examples, maybe you can play around with GROUP BY and HAVING
to get exactly what you're looking for (easier to not have years in
sequential order with group by/having count(*) > 2 - gives you any 3
years repeated)

-----Original Message-----
From: sqr-users-bounces+bstone=fastenal.com@sqrug.org
[mailto:sqr-users-bounces+bstone=fastenal.com@sqrug.org] On Behalf Of
Brooke Funk
Sent: Monday, June 26, 2006 4:02 PM
To: sqr-users@sqrug.org
Subject: [sqr-users] Consecutive Years query

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