<!--- This will show you how to query a query --->
<!--- What this page does is query my db for all tickets entered between
2 dates and then it takes those tickets and finds the ones that have the specific
value of "closed" and then takes the time between those dates for each ticket and calculates
the avg time it took for someone to close the ticket --->

<!--- This is where you select all records from date1 thru date2 --->
<cfquery name="received" datasource="database">
    select RecvdDate, CallStatus
    from database
    where recvddate between <cfqueryparam value="2003-05-01">
       and <cfqueryparam value="2004-05-01">
</cfquery>

<!--- This is where you query the above query and take the data that is returned from it and
Query it again with another instance --->
<CFQUERY NAME="closed" dbtype="query">
    select RecvdDate, CallStatus
    from received
    where callstatus = 'Closed'
</CFQUERY>

<!--- These two queries basically find the number of tickets opened in a 1yr period
And then finds how many of the tickets were closed during that time --->

<!--- This is where you find the average time it took to close a ticket --->
<cfset diffcount = 0>
<cfoutput query="closed">
    <!--- diffcount is eq to 0 + the the # of 'days' between the two dates --->
    <cfset diffcount = diffcount + dateDiff('d', recvddate, closeddate)>
</cfoutput>
<cfset dateavg = diffcount/closed.recordcount>

<!--- This is the output --->

<table>
    <tr>
        <th>Between 05/01/2003-05/01/2004</th>
    </tr>
    <tr>
        <td>No. of Tickets Opened:</td>
        <!--- No. of records returned from 1st query --->
        <td><cfoutput>#received.recordcount#</cfoutput></td>
    </tr>
    <tr>
        <td>No. of Tickets Closed:</td>
        <!--- No. of records returned from 2nd query --->
        <td><cfoutput>#closed.recordcount#</cfoutput></td>
    </tr>
    <tr>
        <!--- This is where you find the average - use the round statement to make your data
                look cleaner. It returns #.## instead of #.########## --->
        <td>Avg. Turn-Around:</td>
        <td><cfoutput>#Evaluate(round(dateavg*100)/100)#</cfoutput> Days</td>
    </tr>
</table>