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.
> Currently I create a list of Carrier's then loop
>thru, within the loop I CFQUERY and get counts for each carrier for that
>single day (inner loop), well this results in tons of queries....and could
>potentially be really slow depending on the date range although I limit to
>31 days.
Perhaps you want to look into using a stored procedure? Or Query of a
Query as a backup.
>Output needs to look like this:
>
>CARRIER DATE1 DATE2 DATE3
>carr1 n n n
>carr2 n n n
>carr3 n n n
>carr4 n n n
This should give you something to go off of... :)
--
Jeffry Houser | mailto:[EMAIL PROTECTED]
Need a Web Developer? Contact me!
AIM: Reboog711 | Fax / Phone: Coming soon!
--
My Books: http://www.instantcoldfusion.com
My Band: http://www.farcryfly.com
______________________________________________________________________
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
