?
Solved

Using a cfquery output field listing to drive a loop one at a time to generate a final output table.

Posted on 2007-07-30
2
Medium Priority
?
189 Views
Last Modified: 2013-12-16
I have a full listing of employee ids after performing a cfquery to output it to psidlist.psid.  
I want to run the next sql query looping through the psidlist.psid one at a time, and not one whole group.
I then want to output for each employee as a summarization.

my sql query will work as long as I do one employee from the cfquery output listing, one at a time, otherwise I will err out due to scalar issues.

Question: I know there is a technique using coldfusion to go through the cfquery output listing one at a time, process the one, output the one and then go to the next one.  

I need assistance. I do not remember the techinque. It has been awhile.

If I use your solution, I give full credit.
*************************************************************************************************************************
( I will replace all psid with #psidlist.psid#)

<cfquery name="psidlist"  Datasource="#Request.datasource#">
select psid
from a_rollup_dept
order by psid
</cfquery>


<cfquery name="qdirect" Datasource="#Request.datasource#">
select
   (select psid,

from a_rollup_labor  
    group by "#psidlist.psid#"),

   (select sum(labor_hours) from a_rollup_labor  
   where labor_date = '01/01/2007'
   group by psid) jan_salary,

    (select sum(labor_hours) from a_rollup_labor  
    where d_i IN ('D','I')
    and proj_def in ('##')
    and activity_type like 'D%'
    and rcc NOT in ('80507')
    group by psid) ototal,

   (select sum(labor_hours) from a_rollup_labor  
   where labor_date = '02/01/2007'
   group by psid) feb_salary,
 
 
  (select sum(labor_hours) from a_rollup_labor  
   where labor_date = '03/01/2007'
   group by psid) mar_salary,

      (select sum(labor_hours) from a_rollup_labor
   where labor_date = '04/01/2007'
   group by psid) apr_salary,
   
      (select sum(labor_hours) from a_rollup_labor  
   where labor_date = '05/01/2007'
   group by psid) may_salary,
   
      (select sum(labor_hours) from a_rollup_labor  
   where labor_date = '06/01/2007'
   group by psid) jun_salary
</cfquery>

<cfdump var="#qDirect#">
0
Comment
Question by:mahpog
2 Comments
 
LVL 14

Accepted Solution

by:
Scott Bennett earned 2000 total points
ID: 19594485
I would just skip the first query and so something like this:

<cfquery name="qdirect" Datasource="#Request.datasource#">
SELECT psid,
SUM(CASE labor_date WHEN '01/01/2007' THEN labor_hours ELSE 0 END) AS jan_salary,
SUM(CASE labor_date WHEN '02/01/2007' THEN labor_hours ELSE 0 END) AS feb_salary,
SUM(CASE labor_date WHEN '03/01/2007' THEN labor_hours ELSE 0 END) AS mar_salary,
SUM(CASE labor_date WHEN '04/01/2007' THEN labor_hours ELSE 0 END) AS apr_salary,
SUM(CASE labor_date WHEN '05/01/2007' THEN labor_hours ELSE 0 END) AS may_salary,
SUM(CASE labor_date WHEN '06/01/2007' THEN labor_hours ELSE 0 END) AS jun_salary,
SUM(CASE WHEN (d_i IN ('D','I')
            and proj_def in ('##')
            and activity_type like 'D%'
            and rcc NOT in ('80507'))
      THEN labor_hours ELSE 0 END ) as oTotal
 FROM a_rollup_labor
 GROUP BY psid
 ORDER BY psid
</cfquery>



<cfdump var="#qDirect#">
0
 

Author Comment

by:mahpog
ID: 19594781
S Bennett.

That works!  Cleaning up the SQL as you did was actually a better idea.  I now want to mass load  a table that already exists with the results of this query before displaying. I will open a new question for that.

0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
Geo-targeting is the practice of distributing content based on a person’s location, as best as you can determine it. Let’s look at some ways you could successfully use this tactic. The following tips and case studies could lead to meaningful results.
The purpose of this video is to demonstrate how to connect a WordPress website to Google Analytics. This will be demonstrated using a Windows 8 PC Go to your WordPress login page. This will look like the following: mywebsite.com/wp-login.php :…
The purpose of this video is to demonstrate how to make a WordPress Site faster and smaller in size by cleaning up the database. This will be demonstrated using a Windows 8 PC. Plugin WP Optimize will be used. Go to your WordPress login page. T…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question