Search code examples
coldfusioncoldfusion-8

How to prevent multiple iterations of the same CFQuery in a CFLoop and format into a matrix

I have researched this quite a bit and haven't found quite the answer I'm looking for. I was able to get to this point with a little help but now am stuck at yet another hurdle.

I would like to be able to display all UNIQUE company names down the leftmost column, then a 'tally' of the number of tests of each type conducted within a specific month. I have commented out a bit of my code that, although it works, doesn't account for when the number of a particular test equals zero (no records in db that fit the conditions), which is needed for, if nothing else, to make sure the other tests don't get their 'tallies' mixed up. My code will probably help to elaborate:

<cfset s = structNew()>
<cfquery datasource="test" name="qry">
    SELECT company, test_type, count(test_type) as counter 
    FROM donor_log
    GROUP BY company, test_type
</cfquery>

<cfloop query="qry">
    <cfset s[qry.company][qry.test_type] = qry.counter>
</cfloop>

<table border="1">
    <tr>
        <th>Company</th>
        <th>UA</th>
        <th>BA</th>
        <th>Hair</th>
        <th>Blood</th>
    </tr>
    <cfoutput>
    <cfloop collection="#s#" item="i">
        <tr>
            <td>#i#</td>
            <cfloop collection="#s[i]#" item="j">
                <cfquery dbtype="query" name="ua">
                    SELECT DISTINCT company 
                    FROM qry 
                    WHERE company='#i#' 
                      AND test_type='UA'
                </cfquery>
                <cfdump var="#ua#"> 
                <!---
                <cfif val(s[i][j]) EQ 0>
                    <td>0</td>
                <cfelse>  
                    <td>#s[i][j]#</td>
                </cfif>
                 --->
            </cfloop>  
        </tr>
    </cfloop> 
    </cfoutput>

The commented section doesn't print '0' to the screen when the structKey value equals 0 and presently, if a company has, say, 0 UA's but 2 BA's, the cell under UA says 2 and BA has nothing. So there is something wrong with my <cfif> statement too, but I'm not sure what. Please understand I am just starting to learn ColdFusion and most of what I have on this particular page is from another kind soul's post on a different forum. I look forward to your replies and input, thanks in advance!

PS quick edit: When I use the <cfdump> as above, the output is 1 whole query for a company with 1 such record company="#i#" AND test_type="UA", 2 queries for a company with 2 such records, 3 queries if 3 matching records, and so on. Ultimately would like this query to run just ONCE for each company "#i#" and then pull the s[i][j].recordcount (wherever it doesn't equal 0) so I can then compare that against a different table (a fee schedule) and generate a total amount to bill each company based on number of tests performed, if that makes sense, but I need to cross this bridge first.

Solution

  • If I'm understanding your question correctly, I'd just reorganize the query like something below 

    <cfquery datasource="test" name="qry">
SELECT company, 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 = 'Blood' THEN 1 ELSE 0 END) AS BloodCount
FROM donor_log
GROUP BY company
</cfquery>

    and then output

    <table border="1">
<tr>
    <th>Company</th>
    <th>UA</th>
    <th>BA</th>
    <th>Hair</th>
    <th>Blood</th>
</tr>
<cfoutput query="qry">
 <tr>
  <td>#qry.Company#</td>
  <td>#qry.UACount#</td>
  <td>#qry.BHCount#</td>
  <td>#qry.HairCount#</td>
  <td>#qry.BloodCount#</td>
 </tr>
</cfoutput>