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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
