CF SQL Creating a Table with different results

Question

I am trying to create a table using coldfusion and sql. The table I am trying to create looks like this:

<cfquery datasource="#application.dsn#" name="someprocessTable">
    SELECT *
    FROM checklists
</cfquery>

<table id="Checklist_Stats">
    <thead>
        <th><b>Associate Name</b></th>
        <th><b>Location</b></th>
        <th><b>Checklists Generated by Associate</b></th>
        <th><b>Checklists Generated by Selected Location(s)</b></th>
        <th><b>Associate Percentage of Location Total</b></th>   
    </thead>
    <tbody>
        <cfoutput query="someprocessTable">                     
            <tr>
                <td>#associate#</td>
                <td>#location_code#</td>
                <td>#associate.recordcount#</td>
                <!---<td>##</td>
                <td>##</td>--->
            </tr>                      
        </cfoutput>
    </tbody>
</table>

The part I am unsure about is how do I loop all of this information under one table? Because you would not want to have the same persons name keep reoccurring on the table and then how do you show how many was generated by them since I could not do something like #associate.recordcount#

The group attribute of cfoutput will help. However, putting `Checklists Generated by Selected Location(s)` into data organized by associates doesn't make sense. Finally, your `` tags are unnecessary. — Dan Bracuk, Mar 28 '16 at 16:32
Its because they want to see `Checklists Generated by Associate` total number checklists, then `Checklists Generated by Selected Location(s)` checklists based on the locations that were chosen, then percentage of Locations total -- which that part doesnt make sense to me at all since if they choose more than one location its a percentage of just those locations... — David Brierton, Mar 28 '16 at 18:56
@DanBracuk I am still unsure how I would count the entries for checklists generated by associate any guidance? since `associate.recordcount` is not possible — David Brierton, Mar 28 '16 at 19:44
Data broken down by associate is different than data broken down by location. Even if you are going to display them on the same page, they have to be displayed separately. — Dan Bracuk, Mar 29 '16 at 10:58

Viv · Accepted Answer · 2016-03-29T02:18:26.073

There seems more than one way to do what you want to achieve like doing a query with joins and groups then dump in table; manage your output with a single CFoutput or use nested CFOutput and/or CFloop. Following show third approach:

<table border="1" id="Checklist_Stats">
    <thead>
        <th><b>Associate Name</b></th>
        <th><b>Location</b></th>
        <th><b>Checklists Generated by Associate</b></th>
        <th><b>Checklists Generated by Selected Location(s)</b></th>
        <th><b>Associate Percentage of Location Total</b></th>   
    </thead>
    <tbody>
    <cfquery name="allAssociatesQry" dbtype="query">
        SELECT DISTINCT associate, COUNT(*) AS associateCount FROM someprocessTable GROUP BY associate ORDER BY associate 
    </cfquery>
    <cfloop query="allAssociatesQry">
        <cfquery name="allLocCodeForAssociateQry" dbtype="query">
            SELECT * FROM someprocessTable WHERE associate='#associate#' ORDER BY location_code
        </cfquery>
        <tr><td><cfoutput>#allLocCodeForAssociateQry.associate#</cfoutput></td>
        <cfoutput query="allLocCodeForAssociateQry" group="location_code">
            <cfset locCntr = 0 />
            <cfoutput>
                <cfset locCntr = locCntr + 1 />
            </cfoutput>
            <cfif allLocCodeForAssociateQry.currentRow NEQ 1>
                <tr><td>&nbsp;</td>
            </cfif>
                <td>#allLocCodeForAssociateQry.location_code#</td>
                <td>#allAssociatesQry.associateCount#</td>
                <td>#locCntr#</td>
                <td>#Round((locCntr/allAssociatesQry.associateCount) * 100)#%</td>
            </tr>                      
        </cfoutput>
    </cfloop>
    </tbody>
</table>

Please note that CF QoQ is case sensitive so if need be then convert associate name and location to lower/upper/title case before hand

A slightly modified code for the CFloop may be like below:

<cfloop query="allAssociatesQry">
    <cfset thisAssociateName = trim(allAssociatesQry.associate) />
    <cfquery name="allLocCodeForAssociateQry" dbtype="query">
        SELECT location_code,count(location_code) AS locCntr FROM someprocessTable WHERE associate='#thisAssociateName#' GROUP BY location_code ORDER BY location_code
    </cfquery>
    <cfoutput query="allLocCodeForAssociateQry">
        <tr>
            <td>#thisAssociateName#</td>
            <td>#allLocCodeForAssociateQry.location_code#</td>
            <td>#allAssociatesQry.associateCount#</td>
            <td>#allLocCodeForAssociateQry.locCntr#</td>
            <td>#Round((allLocCodeForAssociateQry.locCntr/allAssociatesQry.associateCount) * 100)#%</td>
        </tr>
        <cfset thisAssociateName = "" />
    </cfoutput>
</cfloop>

Hey Viv for associate name for some reason whoever set up the database allows 028 and 28 as a user. Is it possible in this query to check for that and realize that those people are one in the same? — David Brierton, Mar 29 '16 at 14:43
Will be tricky but depend on the data. Do all associate names are numbers? I mean if you have only numbers for 'associate' then you can do a CONVERT/CAST (MS SQL) all to INT in your original query. Else you can convert all to varchar with fixed width with padded zeros (0). While showing the data you can remove those extra zeros by using val() function. If you have 028/28 along with other varchar values then I would say that you use some CASE in your original SQL query to convert values like in this case remove '0' from '028'. Not a very good solution but may work. — Viv, Mar 29 '16 at 16:09
You should avoid executing any kind of query within a loop, unless there is a good reason you cannot use a JOIN and a single query. Executing 20, 30, 50+ queries for every request will not scale well. — Leigh, Mar 29 '16 at 18:03
DB Query - I would agree but here loop is executing in-memory query. I am not sure we will have the same scaling issue here too. — Viv, Mar 29 '16 at 18:11
i have to query another database to match the number and display the name with last name, first name that matches the number. Except I have to use databases that already exist which I do not necessarily like because they allowed numbers like 028 and 28 to be the same person I would think you would want to trim that before submitting into the database — David Brierton, Mar 29 '16 at 18:17
@Viv - In memory queries are not magic ;-) and they aren't exempt from scaling issues, granted less network related than w/db queries. http://stackoverflow.com/questions/13632246/cf-qoq-vs-query — Leigh, Mar 29 '16 at 19:03
so you guys are saying the example above is not the best way to do it since there is a query in the loop? — David Brierton, Mar 29 '16 at 19:14
@DavidBrierton - Typically (though not always) databases do a better job handling data than QoQ's. Especially when you are looping, which means performing the query once for *every* row in the query - for each request. So looping should be avoided - if at all possible. Without knowing anything about your environment, hard to say if it is avoidable or not. What is your dbms? Is a JOIN possible? Are the databases on the same server and/or accessible to each other? — Leigh, Mar 29 '16 at 20:01

CF SQL Creating a Table with different results

1 Answers1

Linked