you can also use select distinct to be sure you only list each record/total once.
-----Original Message----- From: Jeffry Houser [mailto:[EMAIL PROTECTED]] Sent: Saturday, May 25, 2002 7:34 AM To: CF-Talk Subject: Re: must be a better method My one thing to add here is that if you can re-work the database to keep date and times separate, it might be beneficial in the long-run. I've had problems in the past creating daily reports because I didn't have isolation of the date and time. At 09:49 PM 5/24/2002 -0500, you wrote: >adrian, > >this should work... > >SELECT carrier, cast(convert(char(8),mydate,112) as datetime) as >date_shipped, count(mydate) AS totals >FROM carrier >WHERE mydate BETWEEN '05/1/2002 00:00' AND '05/15/1999 23:59' >GROUP BY carrier,cast(convert(char(8),mydate,112) as datetime) >ORDER BY carrier,cast(convert(char(8),mydate,112) as datetime) > >~ dina > >----- Original Message ----- >From: "Adrian Cesana" <[EMAIL PROTECTED]> >To: "CF-Talk" <[EMAIL PROTECTED]> >Sent: Friday, May 24, 2002 5:49 PM >Subject: RE: must be a better method > > > > 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. > > > > > > ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
