I need some help from your SQL gurus out there :)
Here's the deal: we are keeping a running tally of the sermons which have
been downloaded the most over the past 30 days. Every time a sermon is
downloaded, I write a record out to a sermons_details table, where the
primary key is the sermon number. This is working fine:
<cfset TestDate = CreateODBCDate(Now())>
<cfset Last30Days = '#DateAdd("d",-30,"#TestDate#")#'>
<cfquery name="sermon_nums" datasource="maindsn" maxrows="5">
SELECT sermons_details.sermon_num, count(sermons_details.sermon_num) AS
tally
FROM sermons_details
WHERE sermons_details.date_stamp >= #Last30Days#
AND sermons_details.subject = 'SERMONACCESS'
GROUP BY sermons_details.sermon_num
ORDER BY count(sermons_details.sermon_num) DESC
</cfquery>
This counts up the number of detail records, and orders them. Works like a
champ. The difficulty comes in when I try to get the info from the header
record for each sermon:
<cfquery name="most_requested" datasource="maindsn" maxrows="5">
SELECT sermons.title, sermons.tape, sermons.name, speakers.initials,
sermons.size
FROM sermons, speakers
WHERE tape IN (<cfloop query="sermon_nums">'#sermon_num#'<cfif
nums.currentrow LT 5>,</cfif></cfloop>)
AND sermons.size > 0 --->
AND speakers.full_name = sermons.speaker
</cfquery>
This gets the right info, but now the ordering is off -- the second query
isn't ordered according to which sermon was downloaded most.
I'm assuming there is a way to do all of this with a single query, but so
far I haven't been able to figure it out. Oh yeah, I'm (still) using
Access2000, so the functionality of the DB is a bit limited too. Ideas?
TIA,
David
------------------------------------
David Grabbe
Manager, Information Systems
Church of the Great God
[EMAIL PROTECTED]
http://www.cgg.org
______________________________________________________________________
Signup for the Fusion Authority news alert and keep up with the latest news in
ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
