You didn't say what DB you are using but this should work on SQL Server ...
select count(id) as Num, datepart(ww, date) as weekofyear, year(date) as year from main where date > #firstDate# and date < #lastDate# group by datepart(ww, date), year(date) order by year, weekofyear </rob> -----Original Message----- From: Venable, John [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 5:25 PM To: CF-Talk Subject: Recursive Query: Anything better?? I'm sure there is something better than what I'm doing, basically I want to do a weekly count of how many people have signed up for our newsletter. Here's what I have, it seems VERY inefficient. Any SQL gurus out there wanna help me out? Thanks in advance. <cfset end="5/19/01"> <cfloop condition="#end# LT now()"> <cfquery name="stats" datasource="email" dbtype="OLEDB" cachedwithin="#CreateTimeSpan(30, 0, 0, 0)#"> SELECT count(id) as num FROM MAIN WHERE date between '#dateformat(DateAdd("d", -7, end))#' AND '#dateformat(end, "mm/dd/yy")#' </cfquery> <cfoutput query="STATS"> <td align="center" valign="bottom">[formatting clipped for clarity]</td> </cfoutput> <cfset end="#DateAdd("d", +7, end)#"> </cfloop> John Venable ______________________________________________________________________ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.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
