-----Original Message-----
From: Eric Creese [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 06, 2003 3:41 PM
To: CF-Talk
Subject: SQL Challenge
Ok here is the challenge. Surprise is at the end.
I am trying to put together a report on our member's membership periods.
This report needs to identify the entire member's membership history.
Memberships are based on an annual subscription. However, there are sometime
breaks in one's membership due to cancellation, expired, whatever. So I will
give 2 examples of some memberships.
Example Member A joined 7/1/2001 and their current expiration is 7/31/2004.
They have been good member for 3 years and renewed every year without
incident. In my history table I have the following for Member A's EXPIRATION
DATES
OLDVAL NEWVAL
07/31/2003 07/31/2004
07/31/2002 07/31/2003
07/31/2001 07/31/2002
Example Member B joined 7/1/2001 and their current expiration is 8/31/2004.
This member forgot to renew one year and rejoined in August. In my history
table I have the following for Member B's EXPIRATION DATES
OLDVAL NEWVAL
07/31/2003 08/31/2004
07/31/2002 07/31/2003
07/31/2001 07/31/2002
So on my report I would like to see the following to determine how long
their memberships have been contiguous and gauge how long between rejoins
for lapses in membership. This data could be kept in a temporary table.
MEMBER A 07/1/2001 07/31/2004
MEMBER B 08/1/2001 08/31/2004
MEMBER B 07/1/2001 07/31/2003
If someone can provide me a solution I will send them a surprise via mail,
we got some cool stuff around my place of business.
Eric
_____
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
