I'm trying to output a table with quantities of assessments taken over a 3
year period. Specifically the last 3 years so i wont always know what the
year is until I output.
<cfquery name="totals" datasource="#DSN#">
SELECT DISTINCT
Count(*) as "total",
Type, --Varchar
year(date_time) As "year" --Date
FROM assessment
Where Year(date_time) >= year(DATEADD("YYYY", -3, getdate()))
Group by
type,
year(date_time)
Order by
type,
year(date_time) Asc
</cfquery>
My problem is if a year had no types it has no value not even null. How
would I output the following table and Include years with No value? back end
is SQL 05 & CFMX7
<table>
<TR>
<td></td>
<th>2005</th>
<th>2006</th>
<th>2007</th>
</tr>
<tr>
<th>Type 1</th>
<td>20</td>
<td>25</td>
<td>30</td>
</tr>
<tr>
<th>Type 2</th>
<td>25</td>
<td>30</td>
<td>35</td>
</tr>
<tr>
<th>Type 3</th>
<td>30</td>
<td>35</td>
<td>40</td>
</tr>
</table>
--
--
Richard Dillman
[EMAIL PROTECTED]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Check out the new features and enhancements in the
latest product release - download the "What's New PDF" now
http://download.macromedia.com/pub/labs/coldfusion/cf8_beta_whatsnew_052907.pdf
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:288566
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
