I'm still learning how to get my groups of results from a query the way I want them. I have looked at scores of examples online but none quite fit my needs. I'm looking for simply the most direct way to pull a set of records with a certain date range from a table, sub-filter them by company name, and then sub-filter THOSE by count of the test_type field. It needs to be dynamic (NON-dynamic on test type; those are set and will not change) because the number of companies is ever-growing. This problem is very similar to another one I posted here about a month ago, only now I need to further (super-?)filter my results by date of service (field name 'dos'). Here's my present code (non-working; errors out w/ "donor_log.company" is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.").
<cfquery name="invall" datasource="test">
SELECT company,dos, SUM(CASE WHEN test_type = 'UA' THEN 1 ELSE 0 END) AS UACount,
SUM(CASE WHEN test_type = 'BA' THEN 1 ELSE 0 END) AS BACount,
SUM(CASE WHEN test_type = 'Hair' THEN 1 ELSE 0 END) AS HairCount,
SUM(CASE WHEN test_type = 'Bld' THEN 1 ELSE 0 END) AS BldCount,
SUM(CASE WHEN test_type = 'DNA' THEN 1 ELSE 0 END) AS DNACount,
SUM(CASE WHEN test_type = 'Pat' THEN 1 ELSE 0 END) AS PatCount,
SUM(CASE WHEN test_type = 'Phys' THEN 1 ELSE 0 END) AS PhysCount,
SUM(CASE WHEN test_type = 'Obs' THEN 1 ELSE 0 END) AS ObsCount
FROM donor_log
WHERE dos BETWEEN '2013-03-01' AND '2013-03-13'
GROUP BY dos
ORDER BY dos DESC
</cfquery>
<table border="1">
<tr>
<th>Company</th>
<th>UA</th>
<th>Obs. UA</th>
<th>BA</th>
<th>Hair</th>
<th>Blood</th>
<th>DNA</th>
<th>Pat.</th>
<th>Phys.</th>
</tr>
<cfoutput query="invall" group="dos">
#dateformat(invall.dos,"mm-dd-yyyy")#
<tr>
<td>#invall.company#</td>
<td>#invall.UACount#</td>
<td>#invall.ObsCount#</td>
<td>#invall.BACount#</td>
<td>#invall.HairCount#</td>
<td>#invall.BldCount#</td>
<td>#invall.DNACount#</td>
<td>#invall.PatCount#</td>
<td>#invall.PhysCount#</td>
</tr>
</cfoutput>
</table>
I understand it is not filtering by company because it doesn't like two non-aggregate columns in the select statement but I'm wondering how to alter this to get something like so (this 'webpage' is to be an invoicing summary page for our various clients but for our reference, not to actually bill out):
03-13-2013
UA BA HAIR BLD (ETC...)
COMPANY A 3 2 0 0
COMPANY B 1 0 0 0
COMPANY C 2 4 0 0
03-12-2013
UA BA HAIR BLD
COMPANY A 1 0 0 0
COMPANY C 2 1 1 0
03-11-2013
UA BA HAIR BLD
COMPANY B 1 1 1 0
COMPANY D 4 2 2 1
...and so on until 03-01-2013. I already have code in place on a different page that I can reuse to allow dynamic date selection based on end-user's preference, so the March dates are arbitrary. Should I be using a subquery here? Using cfoutput/cfloop in a different way/permutation? Looking forward to your responses and thanks in advance, as always! :)
PS: Using ColdFusion 8 and SQL Server 2005.
You are actually pretty close with the group attribute of cfoutput. You just didn't finish what you started. I think you might want something like this.
<cfoutput query="invall" group="dos">
output data for this grouping
<cfoutput group="company">
output data for this grouping
<cfoutput>
output ungrouped data
</cfoutput>
</cfoutput>
</cfoutput>
Your query will have to be ordered by dos desc, company.