Sunday, September 14, 2008

Querying a Query in coldfusion

Querying a Query
<!--- 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">

<!--- 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'
<!--- 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)>
<cfset dateavg = diffcount/closed.recordcount>

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

<th>Between 05/01/2003-05/01/2004</th>
<td>No. of Tickets Opened:</td>
<!--- No. of records returned from 1st query --->
<td>No. of Tickets Closed:</td>
<!--- No. of records returned from 2nd query --->
<!--- 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>

No comments: