#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    22
    Rep Power
    0

    Loop thru DISTINCT, then loop through recordcounts WHERE x = 'y'


    Hello DevShed,

    I am relatively new to CF but have a fairly decent understanding of how to perform CRUD operations and so forth, and I'm learning at a pretty decent pace, but I was tasked with something yesterday and after a full day of testing QoQ and cfoutput w/ query/group attribs, I am still at a loss on how to proceed.

    What I'm trying to do is to take data from the MSSQL table dbo.donor_log, which in it's raw form (some fields left out for brevity) looks like so:

    Code:
    UID   TEST_TYPE   COMPANY
     1    UA          COMP_A
     2    BA          COMP_A
     3    UA          COMP_B
     4    BA          COMP_B
     5    UA          COMP_C
     6    UA          COMP_C
     7    BA          COMP_C
    ...and so on. They are further sorted by timestamp but this is irrelevant to the current issue.

    What I would like to do is make the markup look like this:

    COMP_A
    UA BA
    1 1

    COMP_B
    UA BA
    1 1

    COMP_C
    UA BA
    2 1

    ...and here is my (horribly failing) code:

    Code:
    <html>
    <cfquery name="invd" datasource="test">
      SELECT DISTINCT company FROM donor_log WHERE (dos BETWEEN '2013/01/01' AND '2013/01/31')
    </cfquery>
    
    <cfquery name="ttd" datasource="test">
      SELECT DISTINCT test_type FROM donor_log WHERE (dos BETWEEN '2013/01/01' AND '2013/01/31')
    </cfquery>
    
    <cfquery name="inv" datasource="test">
      SELECT * FROM donor_log WHERE (dos BETWEEN '2013/01/01' AND '2013/01/31')
    </cfquery>
    
    <body>
    <table width="95%">
    <cfoutput>
    <cfloop query="invd">
      <tr><td><h3>#company#</h3></td>
      <cfloop query="inv">
        <cfquery dbtype="query" name="#invd.company#">
          SELECT test_type FROM inv WHERE company='#invd.company#' AND test_type='UA'
        </cfquery>
        <h3>#invd.company.recordcount#</h3> 
      </cfloop>  
      
      <tr>
    </cfloop> 
    
    </cfoutput></table></body></html>
    Granted I am new to CF, but I'm just not seeing any kind of solution here. If anyone could point out what I'm doing wrong (and also if you'd like to see some of my other non-working attempts) I'd greatly appreciate your pointers/solutions. Looking forward to your responses. Thanks in advance!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    11
    A little post-query processing can sometimes save you the headache of an overly complicated SQL query. You might try something like this:

    Code:
    <cfset s = StructNew()>
    
    <cfquery datasource="source" name="qry">
    	SELECT company, test_type, count(test_type) as counter 
    	FROM table
    	GROUP BY company, test_type
    </cfquery>
    
    <cfloop query="qry">
    	<cfset s[qry.company][qry.test_type] = qry.counter>
    </cfloop>
    
    <table border="1">
    <cfoutput>
    <cfloop collection="#s#" item="i">
    	<tr><th colspan="2">#i#</th></tr>
    	<tr>
    	<cfloop collection="#s[i]#" item="j">
    		<td>#j#</td>
    	</cfloop>  
    	</tr>
    	<tr>
    	<cfloop collection="#s[i]#" item="j">
    		<td>#s[i][j]#</td>
    	</cfloop>  
    	</tr>
    </cfloop> 
    </cfoutput>
    </table>

    Comments on this post

    • kiteless agrees
    Last edited by rawk; February 1st, 2013 at 05:49 PM.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    22
    Rep Power
    0
    Rawk,

    Thank you, I will try that out next chance I get and reply here as to how it worked.
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    22
    Rep Power
    0
    Rawk,

    That rawks! Worked like a charm, thanks pal. I'm not sure I ever would have thought to do that but either way, you saved my bacon. You have my gratitude.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    22
    Rep Power
    0
    Edit: Actually, there's another piece to this puzzle...

    Because there are actually more than 2 'test_type's (drug and alcohol tests, in case you were wondering) and in the cases where there are 0 tests of a certain type, I still need to display that in what equates to a spreadsheet in HTML, either a dash (preferred) or a 0. I tried adapting your code to this w/ a statement like so:

    Code:
    <cfif s[i][j] EQ 0 AND j EQ 'UA'>
       <td> - </td> 
    <cfelse> 
       <td>#s[i][j]#</td> 
    </cfif>
    ...however the result was not as I had hoped. It displayed a kind of warped table where all values were the same for each company (UA = 3, BA = 3, Hair = 3, etc.). There are actually 9 different test types but again for brevity's sake I've only included a few. I'm sure if you all can nudge me in the right direction w/ the CF (and the HTML structuring, I'm kinda new to HTML4 also). Here is the new and improved code:

    Code:
    <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>
    <cfoutput>
    <cfloop query="qry">
    	<cfset s[qry.company][qry.test_type] = qry.counter>
    </cfloop>
    
    <table border="1" width="95%">
      <tr>
        <th colspan = "2">COMPANY</th>
        <th>UA</th>
        <th>BA</th>
        <th>Hair</th>
        <th>Blood</th>
      </tr>
    
      <tr>
      <cfloop collection="#s#" item="i">
        <td colspan="2">
          #i#
        </td>
        <cfloop collection="#s[i]#" item="j">
          <cfif s[i][j] EQ 0 AND #j# EQ 'UA'>
            <td>-</td>
          <cfelse>    
            <td>#s[i][j]#</td>
          </cfif>
          <cfif s[i][j] EQ 0 AND #j# EQ 'BA'>
            <td>-</td>
          <cfelse>    
            <td>#s[i][j]#</td>
          </cfif>
          <cfif s[i][j] EQ 0 AND #j# EQ 'Hair'>
            <td>-</td>
          <cfelse>    
            <td>#s[i][j]#</td>
          </cfif>
          <cfif s[i][j] EQ 0 AND #j# EQ 'Blood'>
            <td>-</td>
          <cfelse>    
            <td>#s[i][j]#</td>
          </cfif>
        </cfloop>
        </tr>
    </cfloop>  
    </cfoutput>
    While Rawk's suggestion helped me get over a major hurdle, I'm still having some trouble so anyone's input or help would be much appreciated, thank you in advance!

    Edit 2: When I mentioned spreadsheet above I forgot to explain the formatting. What would be most preferable would be:

    Code:
    COMPANY   UA   BA   HAIR   BLD
    COMP_A     1     1     0       0
    COMP_B     2     3     2       1
    COMP_C     0     2     1       0
    Something like that. Thanks folks, looking forward to your input!
    Last edited by BrandonG; February 4th, 2013 at 12:28 PM. Reason: Add'l info @ formatting
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    11
    Brandon, glad I could help.

    If I'm following you correctly, you're having the following issues moving forward:

    1. zero counts don't return on the grouping query
    2. displaying "-" instead of zero in the table

    Like many problems, there's multiple ways to approach it. In this case, I would start by initializing a struct with all of the possible test types. This requires 1 more query and 1 more post-processing loop. This way, when there are no tests for a given company/type, you will have a zero in the struct instead of a missing struct key. Below is the code for the fully dynamic approach. If you know the test types will never change and you will never add/substract types, then you might be able to simplify the code a bit.

    Code:
    <cfset s = StructNew()>
    <cfset typeS = StructNew()>
    
    <cfquery datasource="source" name="initQry">
    	select distinct test_type
    	from test
    </cfquery>
    
    <cfquery datasource="source" name="qry">
    	select company, test_type, count(test_type) as counter 
    	from test group by company, test_type
    </cfquery>
    
    <cfloop query="initQry">
    	<cfset typeS[initQry.test_type] = 0>
    </cfloop>
    
    <cfloop query="qry">
    	<cfif not StructKeyExists(s, qry.company)>
    		<cfset s[qry.company] = StructCopy(typeS)>
    	</cfif>
    	<cfset s[qry.company][qry.test_type] = qry.counter>
    </cfloop>
    
    <cfoutput>
    <!--- old table --->
    <table border="1">
    <cfloop collection="#s#" item="i">
    	<tr><th colspan="#initQry.RECORDCOUNT#">#i#</th></tr>
    	<tr>
    	<cfloop collection="#s[i]#" item="j">
    		<td>#j#</td>
    	</cfloop>  
    	</tr>
    	<tr>
    	<cfloop collection="#s[i]#" item="j">
    		<td align="center">#s[i][j]#</td>
    	</cfloop>  
    	</tr>
    </cfloop> 
    </table>
    
    <!--- new table --->
    <table border="1">
    	<tr><th>Company</th>
    		<cfloop collection="#typeS#" item="i">
    			<th>#i#</th>
    		</cfloop>
    	</tr>
    	<cfloop collection="#s#" item="i">
    		<tr><td>#i#</td>
    			<cfloop collection="#s[i]#" item="j">
    				<td><cfif s[i][j] neq 0>#s[i][j]#<cfelse>-</cfif></td>
    			</cfloop>
    		</tr>
    	</cfloop>
    </table>
    </cfoutput>
    The only difference between the new table and the example you posted is the order of the test type columns. If the order doesn't matter, then you should be good to go. If you need a specific order for these columns AND it needs to be a dynamic set of types, then it becomes a good bit more complicated (you will need an "order_index" field for your test types). And finally, if order matters but it does NOT need to be dynamic, then you can go with a simplified approach where test types are hardcoded into your tables and your sql queries.

    Hopefully this gets you on the right track. Let me know if I can be of further assistance.

    Comments on this post

    • BrandonG agrees
    Last edited by rawk; February 4th, 2013 at 05:17 PM.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    22
    Rep Power
    0
    Rawk,

    You gave me a lot more here than I could have hoped for, in fact I think I'll be posting at these forums more often based on this! Yes, that works quite well and it is a finite number of tests so I will probably go w/ the hard-coded approach on setting up a consistent order for the values to fall in.

    I will also be documenting your solutions (hope you don't mind) in my 'bag of tricks' so that if something similar comes up in the future I will have a framework to work with. Thank you again for your help, you da man!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    84
    Rep Power
    11
    By all means, document away.

    I generally come to these forums with my most challenging questions after I've exhausted google and other resources. It's not uncommon for it to take a day or two to get a response, but I can usually be sure it will be helpful one.

    Occasionally, I'll come by just to see if I'm able to answer other people's questions (or, when I ask one, I try to answer someone else's as well). kiteless normally handles everyone's questions very well, but I like to share the load when I can.

IMN logo majestic logo threadwatch logo seochat tools logo