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. > > ______________________________________________________________________ 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
