CF query issue involving users and locations

I am trying to figure out the best way to properly set up my queries. Basically I have a mini form asking location EmployeeName StartDate and EndDate. I am using two MS SQL Server tables unfortunately on two different servers that I am unable to link.

My goal is to have 5 columns Associate Name | Location Name | Associate Checklists for location | Total Location Checklists | Associate Percentage

The main table I am using is cl_checklists: the three columns I am using are date to check the dates between selected on the form, associate for the EmployeeNames and trans_location for the different locations.

    <cfset result = {} /> 
<cftry> 
    <cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
    <cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

    <cfquery datasource="#application.dsn#" name="GetEmployeeInfo">
        SELECT  *
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
                AND associate IN ( <cfqueryparam value="028,998,28" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

     <cffunction name="getop_id" access="public" returntype="string"> 
        <cfargument name="associate"  > 
        <cfquery name="spitOutop_id" datasource="#application.userinfo_dsn#"> 
            SELECT assoc_name 
            FROM dbo.tco_associates 
            WHERE assoc_id= #arguments.associate# 
        </cfquery> 
        <cfreturn spitOutop_id.assoc_name > 
     </cffunction> 

<table border="1" id="Checklist_Stats">
    <thead>
        <th><strong>Associate Name</strong></th>
        <th><strong>Location</strong></th>
        <th><strong>Checklists Generated by Associate</strong></th>
        <th><strong>Checklists Generated by Selected Location(s)</strong></th>
        <th><strong>Associate Percentage of Location Total</strong></th>   
    </thead>
    <tbody>
    <cfquery name="allAssociatesQry" dbtype="query">
        SELECT DISTINCT associate, COUNT(*) AS associateCount FROM GetEmployeeInfo GROUP BY associate ORDER BY associate 
    </cfquery>
      <cfloop query="allAssociatesQry">
        <cfset thisAssociateName = trim(allAssociatesQry.associate) />
      <cfquery name="allLocCodeForAssociateQry" dbtype="query">
          SELECT trans_location,count(trans_location) AS locCntr FROM GetEmployeeInfo WHERE associate='#thisAssociateName#' GROUP BY trans_location ORDER BY trans_location
      </cfquery>
        <cfoutput query="allLocCodeForAssociateQry">
          <tr>
              <td><strong>#thisAssociateName#</strong></td>
              <!---<td><strong>#getop_id(192)#</strong></td>--->
              <td>#allLocCodeForAssociateQry.trans_location#</td>
              <td>#allLocCodeForAssociateQry.locCntr#</td>
              <td>#allAssociatesQry.associateCount#</td>
              <td>#NumberFormat((allLocCodeForAssociateQry.locCntr/allAssociatesQry.associateCount) * 100, '9.99')#%</td>
          </tr>
          <cfset thisAssociateName = "" />
      </cfoutput>
      </cfloop>
      <tr>
        <td><strong>Total</strong></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
      </tr>
    </tbody>
</table>

<cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry>

I cannot figure out how to change my query for the 4th column the total is showing the users total checklist for that location but instead I want the total checklists for the location in the 4th column. Will someone please tell me where I am going wrong and how I can find the total of that locations instead of the users total at that location which is already the third column.

Can i do a query inside a query like add

<cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT  *
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

and then do a total count adding another output inside the loop?

Answers


The problem is that you are using the allAssociatesQry for your 4th column 'Checklist Generated By Selected Location(s)' which is grouped by associate but you want the total checklist count in your 4th column here to be Grouped on Location and not Associate.

You might want to consider separating them!

Keep the code as is, just add a separate query for calculating the 4th column just after the allLocCodeForAssociateQry query:

<cfquery name="allLocCountForAssociateQry" dbtype="query">
    SELECT trans_location,count(trans_location) AS totalLocCount FROM GetLocationInfo WHERE trans_location IS NOT NULL AND trans_location IN (#QuotedValueList(allLocCodeForAssociateQry.trans_location)#) GROUP BY trans_location
</cfquery>

And now use the filtered value from this query inside your cfoutput query loop for allLocCodeForAssociateQry like this:

<cfoutput query="allLocCodeForAssociateQry">
  <tr>
      <td><strong>#thisAssociateName#</strong></td>
      <!---<td><strong>#getop_id(192)#</strong></td>--->
      <td>#allLocCodeForAssociateQry.trans_location#</td>
      <td>#allLocCodeForAssociateQry.locCntr#</td>
      <!--- Change 4th column to: --->
      <td>#allLocCountForAssociateQry['totalLocCount'][CurrentRow]#</td>
      <td>#NumberFormat((allLocCodeForAssociateQry.locCntr/allAssociatesQry.associateCount) * 100, '9.99')#%</td>
  </tr>
  <cfset thisAssociateName = "" />
</cfoutput>
Update:

David, based on the discussion we had over chat, where you mentioned that you want to show the total for each Associate group result and you want an aggregate of the result at the end of the report.

Based on this, I am posting one of the solutions note: I believe that using grouping in cfloop would result in more simplified and optimal result, but for time's sake I chose to go with the current working solution.

I have also added bunch of comments to make it more readable and clear.

Here is the solution:

<cfset result = {} /> 
<cftry> 
    <cfset date1 = CREATEODBCDATETIME(form.StartDate & '00:00:00')>
    <cfset date2 = CREATEODBCDATETIME(form.EndDate & '23:59:59')>

    <cfquery datasource="#application.dsn#" name="GetEmployeeInfo">
        SELECT  *
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
                AND associate IN ( <cfqueryparam value="028,998,28" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT  *
        FROM    cl_checklists
        WHERE   date >=  <cfqueryparam value="#date1#" cfsqltype="cf_sql_timestamp" />
                AND date <= <cfqueryparam value="#date2#" cfsqltype="cf_sql_timestamp" />
                AND trans_location IN ( <cfqueryparam value="#FORM.location#" cfsqltype="cf_sql_varchar" list="true" />  )
    </cfquery>

     <cffunction name="getop_id" access="public" returntype="string"> 
        <cfargument name="associate"  > 
        <cfquery name="spitOutop_id" datasource="#application.userinfo_dsn#"> 
            SELECT assoc_name 
            FROM dbo.tco_associates 
            WHERE assoc_id= #arguments.associate# 
        </cfquery> 
        <cfreturn spitOutop_id.assoc_name > 
     </cffunction> 

     <cfquery name="allAssociatesQry" dbtype="query">
        SELECT DISTINCT associate, COUNT(*) AS associateCount FROM GetEmployeeInfo GROUP BY associate ORDER BY associate 
     </cfquery>

<table border="1" id="Checklist_Stats">
    <thead>
        <th><strong>Associate Name</strong></th>
        <th><strong>Location</strong></th>
        <th><strong>Checklists Generated by Associate</strong></th>
        <th><strong>Checklists Generated by Selected Location(s)</strong></th>
        <th><strong>Associate Percentage of Location Total</strong></th>   
    </thead>
    <tbody>
      <!--- aggregate variables --->
      <cfset aggrAssociateChecklist = 0>
      <cfset aggrLocationChecklist = 0>

      <cfloop query="allAssociatesQry">
          <!--- get Associate's name --->
          <cfset thisAssociateCode = trim(allAssociatesQry.associate)>
          <cfset thisAssociateName = getop_id(thisAssociateCode) />
          <!--- 1.1 get all trans_location code and total counts for the current Associate --->
          <cfquery name="allLocCodeForAssociateQry" dbtype="query">
              SELECT trans_location,count(trans_location) AS locCntr FROM GetEmployeeInfo WHERE associate='#thisAssociateCode#' GROUP BY trans_location ORDER BY trans_location
          </cfquery>
          <!--- 1.2 get the aggregate of checklist count generated by the current Associate for each location --->
          <cfquery name="qTotalChecklistCountForAssociate" dbtype="query">
              SELECT SUM(locCntr) AS totalAssocChecklist FROM allLocCodeForAssociateQry 
          </cfquery>

          <!--- 2.1 get the total location checklist for each location available for the current Associate --->
          <cfquery name="allLocChecklistForAssociateQry" dbtype="query">
              SELECT trans_location,count(trans_location) AS totalLocCount FROM GetLocationInfo WHERE trans_location IN (#QuotedValueList(allLocCodeForAssociateQry.trans_location)#) GROUP BY trans_location ORDER BY trans_location
          </cfquery>
          <!--- 2.2 get the aggregate of location checklist generated by the current Associate --->
          <cfquery name="qTotalLocChecklistForAssociate" dbtype="query">
              SELECT SUM(totalLocCount) AS totalLocChecklist FROM allLocChecklistForAssociateQry
          </cfquery>
          <!--- display record for the current Associate --->
            <cfoutput query="allLocCodeForAssociateQry">
              <tr>
                  <!---<td><strong>#thisAssociateCode#</strong></td>--->
                  <td><strong>#thisAssociateName#</strong></td>
                  <td>#allLocCodeForAssociateQry.trans_location#</td>
                  <td>#allLocCodeForAssociateQry.locCntr#</td>
                  <td>#allLocChecklistForAssociateQry['totalLocCount'][CurrentRow]#</td>
                  <td>#NumberFormat((allLocCodeForAssociateQry.locCntr/allLocChecklistForAssociateQry['totalLocCount'][CurrentRow]) * 100, '9.99')#%</td>
              </tr>
              <cfset thisAssociateName = "" />
            </cfoutput>
            <!--- 3.1 get sub total for each Associate group --->
            <cfset totalAssocChecklist = qTotalChecklistCountForAssociate.totalAssocChecklist>
            <cfset totalLocChecklist = qTotalLocChecklistForAssociate.totalLocChecklist>
            <!--- 3.2 add to the aggregate --->
            <cfset aggrAssociateChecklist += totalAssocChecklist>
            <cfset aggrLocationChecklist += totalLocChecklist>
            <!--- display sub total for each Associate group --->
            <cfoutput>
                <tr>
                    <td><strong>Subtotal</strong></td>
                    <td></td>
                    <td>#totalAssocChecklist#</td>
                    <td>#totalLocChecklist#</td>
                    <td>#NumberFormat((totalAssocChecklist/totalLocChecklist) * 100, '9.99')#%</td>
                </tr>
            </cfoutput>
      </cfloop>
      <!--- display calculated aggregate at the end of the result --->
      <cfoutput>
          <tr>
            <td><strong>Total</strong></td>
            <td></td>
            <td>#aggrAssociateChecklist#</td>
            <td>#aggrLocationChecklist#</td>
            <td>#NumberFormat((aggrAssociateChecklist/aggrLocationChecklist) * 100, '9.99')#%</td>
          </tr>
      </cfoutput>
    </tbody>
</table>
<cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry>

Need Your Help

Sum function in x86 assembly - no output

function assembly x86 nasm procedure

I am trying to write a simple sum function in x86 assembly - to which i am passing 3 and 8 as arguments. However, the code doesn't print the sum. Appreciate any help in spotting the errors. I'm using

Post to server from Android

android post

Get was quite simple, but I cant seem to get my head around POST. I need some help with sending an image and an int value to server.