Sorry maybe I need to finish reading my emails before I reply. Josh
-----Original Message----- From: Ian Skinner [mailto:[EMAIL PROTECTED] Sent: Thursday, July 03, 2003 10:11 AM To: SQL Subject: Fairly Difficult SQL problem I have tables something like the following data structures. Sponsor ID Sponsor ... 1 Foo 2 Bar 3 Clyde 4 New Sponsor Drives ID SponsorID Date ... 1 1 4/15/03 2 2 4/18/03 3 3 4/24/03 4 2 5/9/03 5 1 5/16/03 6 3 6/3/03 7 2 6/11/03 8 1 6/15/03 9 4 6/18/03 What I need to retreive is all the drives for a given month (June for example) plus the number of days it has been since any previous drive for a given Sponsor. RecordSet Sponsor.ID Sponsor Drives.ID SponsorID Date DaysSinceLastDrive ... 3 Clyde 6 3 6/3/03 40 2 Bar 7 2 6/11/03 33 1 Foo 8 1 6/15/03 30 4 New Sponsor 9 4 6/18/03 N/A Any suggestions on how I could do this in SQL so I don't have to something unpleasant like looping over one query result, running another query each iteration. If it matters this is ColdFusion MX, no updates querying a Oracle 9i database. Thank You -------------- Ian Skinner Web Programmer BloodSource Sacramento, CA ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
