SELECT YEAR(datefield) as year, MONTH(datefield) as month, DAY(datefield) as day, COUNT(anotherfield) as nr FROM tablename WHERE datefield BETWEEN @start AND @end GROUP BY YEAR(datefield), MONTH(datefield), DAY(datefield) ORDER BY year, month, day
*should* work. The reason I split up the date was because you can then do what you pretty much like with it -- like change it do each month for instance.. You could also ORDER BY the nr column as well to get the top "hits". Is this what you wanted? Darryl. -----Original Message----- From: Paris Lundis [mailto:[EMAIL PROTECTED]] Sent: Tuesday, 15 January 2002 3:19 PM To: CF-Talk Subject: cold fusion reporting... does anyone have a good sample of SQL code that does in essence the following: A. takes and groups the total records on a per date basis where the date is a date/time field.. so we want Jan 11, Jan 12, Jan 13, etc. broken out... (yeah we store date and time.. much easier if we would have stored time in another field)... so only group where records exist for day.. B. Calculates the total number of records found for each date... so Jan 11 = 21, Jan 12=31... an Access database sort of SQL piece will be fine... sounds simple... perhaps I should get some rest and come back... :) ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc 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
