> > 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.
Does it need to be in the same query - I might treat that as two separate
queries
- one to retrieve all drives for the given month.
- one to retrieve the most recent drive (drives less than today's date) for
each individual sponsor.
Convert the second query into something you can reference by sponsorID -
like an array whose keys are sponsorID's. Then as you're looping through
your first query you can just reference the array to output the number of
days since the last drive for that given sponsor. Something like -
<cfloop query="qryDrives">
datediff("d",now(),arrayRecentDrive[qryDrives.sponsorID])
</cfloop>
Adam.
> -----Original Message-----
> From: Ian Skinner [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 03, 2003 9:53 AM
> To: CF-Talk
> Subject: Fairly Difficult SQL problem
>
>
> Trying this on the more active CF-Talk list.
>
> ...
>
> > 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=4
Subscription:
http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
http://www.cfhosting.com
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
