You did not specify what the primary key is of your Payments table, so I'll
assume that the combination of DonorSN and Source_Code serves this function.

I'm using SQL Server, which has a DATEDIFF function which calculates the
difference between two dates.  You may have to substitute the equivalent
functionality for your RDBMS (e.g. INTERVAL data type).

I'm also only testing for 'reactivation' between the latest two dates:

	SELECT DISTINCT DonorSN FROM Payments P1
		WHERE DATEDIFF(m,
		  (SELECT MAX(Post_Date) FROM Payments P2
			WHERE P1.DonorSN=P2.DonorSN AND P1.Source_Code=P2.SourceCode
			AND P2.Post_date < (SELECT MAX(Post_Date) FROM Payments P3
				WHERE P1.DonorSN=P3.DonorSN AND P1.Source_Code=P3.SourceCode)),
		  (SELECT MAX(Post_Date) FROM Payments P4
			WHERE P1.DonorSN=P4.DonorSN AND P1.Source_Code=P4.SourceCode))>=12;

I think ;-)

The idea here is that the last subquery (P4) determines the last (maximum)
Post_Date for a given DonorSN/Source_Code from the outermost query.  This is
the 'enddate' for the DATEDIFF function. The 'startdate' is the maximum date
less than this 'enddate'.  The DonorSN is selected if the difference between
these two dates is 12 months or more.

Note also that I got lazy and have not protected this against subqueries
returning NULL in the case where there are less than two distinct dates.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Andrew Ziem [mailto:andrew@s...]
Sent: Monday, October 21, 2002 11:11 PM
To: sql language
Subject: [sql_language] RE: Can SQL do this?


A donor is reactivated by giving a gift after being inactive, i.e. after 12
months of not giving.

A donor is active when he has given a gift within the last 12 months.

A donor is inactive when he has not given a gift within the last 12 months
(but has given a gift before that time).

If "Joe" has given a gift in 1999, he is inactive.  If he gives a gift
today (12 months later or more), he is reactivated.


Andrew


> What is your definition of "reactivated"?  Your 'specification' does not
make this clear.

--
Jeff Mason			Custom Apps, Inc.
Jeff@c...

-----Original Message-----
From: Andrew Ziem [mailto:andrew@s...]
Sent: Saturday, October 19, 2002 5:40 PM
To: sql language
Subject: [sql_language] Can SQL do this?


Hi,

This seems difficult or impossible in pure SQL.  Can someone tell me how
to do it?

Given this payments table:

DonorSN  | Source_Code | Post_Date
x        | yyy         | zz/zz/zzz
.....

How do I find the Source_Codes for Donors who have been "reactivated"?
This means, for a given DonorSN, there may be zero or more instances where
the Post_Date is more than 12 months from the last one.

This is an example of reactivation on code L1002:

DonorSN  | Source_Code | Post_Date
5        | L1000       | 10/1/2000
5        | L1002       | 10/1/2002


--
Andrew Ziem, Admin Asst.
THE SPRINGS RESCUE MISSION
+1  (xxx) xxx-xxxx
http://www.springsrescuemission.org