<!--- 
 Filename: ExpenseReport3.cfm
 Created by: Nate Weiss (NMW)
 Purpose: Demonstrates use of SQL aggregate functions
--->

<!--- These values will control the filtering --->
<cfset filterMinTotal = 1000>
<cfset filterMaxIndiv = 50000>

<!--- Retrieve summarized expense data from database --->
<cfquery name="getExp" datasource="#APPLICATION.dataSource#">
  SELECT 
  f.FilmID, f.MovieTitle,
  SUM(ExpenseAmount) AS ExpenseSum
  FROM Expenses e, Films f
  WHERE e.FilmID = f.FilmID
  AND ExpenseAmount < #filterMaxIndiv#
  GROUP BY f.FilmID, f.MovieTitle
  HAVING SUM(ExpenseAmount) > #filterMinTotal#
  ORDER BY f.MovieTitle
</cfquery>


<html>
<head><title>Film Expenses</title></head>
<body>
<h2>Expense Overview</h2>

<cfoutput>
<p>
<!--- Note that we are filtering out small numbers --->
For clarity, films that haven't reported more than 
#lsCurrencyFormat(filterMinTotal, "international")# 
have been ommitted from this list.<br>
In addition, large payments 
(over #lsCurrencyFormat(filterMaxIndiv, "international")#)
have not been considered in the totals.
</p>
</cfoutput>

<!--- Output film-level expense summary --->
<cftable query="getExp" htmltable="Yes" border="Yes" colheaders="Yes">

 <cfcol header="Film Title" text="#MovieTitle#">
 <cfcol header="Amount" text="#lsCurrencyFormat(ExpenseSum)#" align="right">

</cftable>

</body>
</html>
