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

<!--- Retrieve summarized expense data from database --->
<cfquery name="getExp" datasource="#APPLICATION.dataSource#">
  SELECT 
  COUNT(*) AS ExpenseCount,
  MIN(ExpenseDate) AS DateMin,
  MAX(ExpenseDate) AS DateMax,
  MIN(ExpenseAmount) AS ExpenseMin,
  MAX(ExpenseAmount) AS ExpenseMax,
  SUM(ExpenseAmount) AS ExpenseSum,
  AVG(ExpenseAmount) AS ExpenseAvg
  FROM Expenses
</cfquery>


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

<!--- Output high-level expense summary --->
<cfoutput>
<b>Number of expenses logged:</b> 
#getExp.ExpenseCount#<br>

<b>Oldest expense on record:</b> 
#lsDateFormat(getExp.DateMin, "mmmm d, yyyy")#
(#dateDiff("m", getExp.DateMin, now())# months ago)<br>

<b>Most recent expense on record:</b> 
#lsDateFormat(getExp.DateMax, "mmmm d, yyyy (dddd)")#<br>

<b>Smallest expense made:</b> 
#lsCurrencyFormat(getExp.ExpenseMin)#<br>

<b>Largest expense made:</b> 
#lsCurrencyFormat(getExp.ExpenseMax)#<br>

<b>Average expense amount:</b> 
#lsCurrencyFormat(getExp.ExpenseAvg)#<br>

<b>Total of all expenses to date:</b> 
#lsCurrencyFormat(getExp.ExpenseSum)#<br>
</cfoutput>

</body>
</html>
