Styjun

I have three queries that are tied together. The final output requires multiple loops over the queries. This way works just fine but seems very inefficient and too complex in my opinion. Here is what I have:

Query 1:

<cfquery name="qryTypes" datasource="#application.datasource#">
 SELECT 
 t.type_id, 
 t.category_id,
 c.category_name, 
 s.type_shortcode
 FROM type t
 INNER JOIN section s 
 ON s.type_id = t.type_id
 INNER JOIN category c 
 ON c.category_id = t.category_id
 WHERE t.rec_id = 45 -- This parameter is passed from form field.
 ORDER BY s.type_name,c.category_name
</cfquery>

Query Types will produce this set of results:

4 11 SP PRES
4 12 CH PRES
4 13 MS PRES
4 14 XN PRES

Then loop over query Types and get the records from another query for each record that match:

Query 2:

<cfloop query="qryTypes">
 <cfquery name="qryLocation" datasource=#application.datasource#>
 SELECT l.location_id, l.spent_amount
 FROM locations l
 WHERE l.location_type = '#trim(category_name)#' 
 AND l.nofa_id = 45 -- This is form field 
 AND l.location_id = '#trim(category_id)##trim(type_id)#'
 GROUP BY l.location_id,l.spent_amount
 ORDER BY l.location_id ASC
 </cfquery>

 <cfset spent_total = arraySum(qryLocation['spent_amount']) />
 <cfset amount_total = 0 />

 <cfloop query="qryLocation"> 
 <cfquery name="qryFunds" datasource=#application.datasource#>
 SELECT sum(budget) AS budget
 FROM funds f
 WHERE f.location_id= '#qryLocation.location_id#' 
 AND nofa_id = 45
 </cfquery>

 <cfscript>
 if(qryFunds.budgetgt 0) 
 amount_total = amount_total + qryFunds.budget;
 
 </cfscript>
 </cfloop>

 <cfset GrandTotal = GrandTotal + spent_total />
 <cfset GrandTotalad = GrandTotalad + amount_total />
</cfloop>

After the loops are completed this is result:

CATEGORY NAME SPENT TOTAL AMOUNT TOTAL
 SP 970927 89613
 CH 4804 8759
 MS 9922 21436
 XN 39398 4602
 Grand Total: 1025051 124410

Is there a good way to merge this together and have only one query instead of three queries and inner loops? I was wondering if this might be a good fit for a stored procedure and then do all data manipulations in there? If anyone have suggestions please let me know.

• 
  qryTypes returns X records


• 
  qryLocation returns Y records

So far you've run (1 + X) queries.

• 
  qryFunds returns Z records

Now you've run (1 + X)(Y) queries.

The more data each returns, the more queries you'll run. Obviously not good.

If all you want is the final totals for each category, in a stored procedure, you could create a temp table with the joined data from qryTypes and qryLocation. Then your last qryFunds is just joined against that temp table data.

SELECT 
 sum(budget) AS budget
FROM 
 funds f
INNER JOIN 
 #TEMP_TABLE t ON t.location_id = f.location_id
AND 
 nofa_id = 45

You could then get other sums off the temp table if needed. It's possible this could all be worked into a single query, but maybe this helps you get there.

Also, a stored procedure can return multiple record sets, so you can have one return the aggregated table amount data and a 2nd return the grand total. This would keep all the calculations on the database and no need for CF to be involved.