Sorting CFQuery Results by Month

Question

Some Backround: ColdFusion9 MSAccess Database

I am trying to create a CFChart to compare data in a table labeled closed_tickets. I am comparing this year's data to previous year's data and would like to chart it by month.

I am querying the table and grabbing all data between the dates of 1/1/2012 and 12/31/2013, basically all of this year and last year. Here is that query.

<!---Query all closed tickets from this year and last year where closed_date GTE #Previous_Year# --->

<CFSET previous_year = #CREATEODBCDATETIME("1/1/2012")#>
<CFSET this_year = #CREATEODBCDATETIME("1/1/2013")#>

<cfoutput>
<cfquery name="get_closed_tickets" datasource="#datasource#">
select *
from closed_tickets
where closed_date >= #previous_year#
</cfquery>
</cfoutput>

<cfoutput>
Total Records:#get_closed_tickets.recordcount#<br />
</cfoutput>

Then I perform a query of queries to spit the data into 2 years, this year and previous year:

<!---QoQ - Get Previous Year's Calls --->
<cfoutput>
<cfquery name="previous_year" dbtype="query">
select *
from get_closed_tickets
where closed_date >= #previous_year# AND closed_date < #this_year#
</cfquery>
</cfoutput>

<!---QoQ - Get This Year's Calls --->
<cfoutput>
<cfquery name="this_year" dbtype="query">
select *
from get_closed_tickets
where closed_date >= #this_year#
</cfquery>
</cfoutput>

2012 Records:<cfoutput>#previous_year.recordcount#</cfoutput><br />
2013 Records:<cfoutput>#this_year.recordcount#</cfoutput><br /><br />

My question is, I'd like to take this data and chart it using CFChart to compare the data from each year, by month. Ideally I'd like the have a chart that would compare January2012 next to January2013 and so on, for each month of the year.

How can this be done?

Thanks, Brian

andremichels andremichels · Accepted Answer · 2014-04-03T17:44:24

Why won't you use SQL "group by"?

It should be something like this:

select count(tickets), closed_date
from   get_closed_tickets
where  closed_date >= #previous_year# 
and    closed_date < #this_year#
group by closed_date

Another important thing. You shouldn't use select * in your queries. Always use columns names. It will increase performance if you don't actually need all of the columns in the database table.

Sorting CFQuery Results by Month

1 Answers