Worked like a charm Rob, Thanks so much!! John Venable
-----Original Message----- From: Rob Baxter [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 11:03 AM To: CF-Talk Subject: RE: Recursive Query: Anything better?? I't probably possible in sql, but it would be a pain. I haven't used it but why don't you check out this UDF I found on CFLib. It looks like exactly what you need. http://cflib.org/udf.cfm?ID=543 Let me know how it works. </rob> -----Original Message----- From: Venable, John [mailto:[EMAIL PROTECTED]] Sent: Wednesday, April 17, 2002 10:49 AM To: CF-Talk Subject: RE: Recursive Query: Anything better?? Rob, I am using SQL Server and this works great. Thanks much. Is there a way to turn weekofyear into a human readable form, such as the Sunday that starts that week?? TIA John Venable -----Original Message----- From: Rob Baxter [mailto:[EMAIL PROTECTED]] Sent: Tuesday, April 16, 2002 5:47 PM To: CF-Talk Subject: RE: Recursive Query: Anything better?? 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 ______________________________________________________________________ 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
