Thanks, this seems to be working well although the output format isnt really what I wanted but I can work around that. The other issue I have now using this method is the grouping is also including the times from the date field, I only want to group on the mmddyyyy portion. I know I need to use DatePart but cant seem to get it to work with the GROUP BY. Any tips....
This is basically the query Im using... SELECT carrier,mydate,count(mydate) AS totals FROM mytable WHERE mydate BETWEEN '05/1/2002 00:00' AND '05/15/2002 23:59' GOUP BY carrier,mydate ORDER BY carrier,mydate Thanks,Adrian -----Original Message----- From: Jeffry Houser [mailto:[EMAIL PROTECTED]] Sent: Friday, May 24, 2002 1:06 PM To: CF-Talk Subject: Re: must be a better method At 12:47 PM 5/24/2002 -0700, you wrote: >Im generating a summary report but the current method Im using could result >in up to 100 queries or more...so this is what Im trying to achieve. > >Basically dealing with two fields, Carrier and Date. The user selects a >date range, I need a count all the records for each day within the range >grouping by the Carrier. I'm not sure if you want: A single count for the specified date range. So, if the range is June 1st through July 1st, you will get 1 count per carrier. Or A count for each day in the specified range. So if the range is June 1st through July 1st, you will get 30 counts per character. This might take some experimentation, but.. something along the lines of: SELECT Count(CarrierTable.DateField) as DateCount, Carrier, DateField FROM CarrierTable WHERE DateField BETWEEN StartDate and EndDate GROUP BY CarrierTable.Carrier, DateField Something like this (off the top of my head ) should give you the output you need: <table> <cfoutput query="MyQuery" group="Carrier"> <tr><td>#MyQuery.Carrier#</td><td> <table><tr> <cfoutput> <td>#MyQuery.DateCount#</td> </cfoutput> </tr></table> </td></tr> </cfoutput> </table> But this could potentially cause problems matching up carriers with date columns if it is possible that a single carrier will have no entries on a date. ______________________________________________________________________ Get the mailserver that powers this list at http://www.coolfusion.com 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
