Query of queries help - This isn't working correctly!

[Guest]

I have two tables...Orders and Sales_Calls. They store similiar data and I am trying to combine their results. Here is my code:

<cfquery datasource="manna_premier" name="orders">
SELECT DISTINCTROW SaleDate,
                   TerritoryManager,
       Orders.UserID,
       Users.UserID,
       Users.UserZone,
       Count(*) AS [Orders]
FROM Users INNER JOIN Orders ON Users.[UserID] = Orders.[UserID]
WHERE Orders.SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
GROUP BY Users.UserZone, Orders.SaleDate, Orders.TerritoryManager, Orders.UserID, Users.UserID;
</cfquery>

<cfquery datasource="manna_premier" name="sales_calls">
SELECT DISTINCTROW SaleDate,
                   TerritoryManager,
       Sales_Calls.UserID,
       Users.UserID,
       Users.UserZone,
       Count(*) AS [Calls]
FROM Users INNER JOIN Sales_Calls ON Users.[UserID] = Sales_Calls.[UserID]
WHERE Sales_Calls.SaleDate BETWEEN #CreateODBCDate(FORM.Start)# AND #CreateODBCDate(FORM.End)#
GROUP BY Users.UserZone, Sales_Calls.SaleDate, Sales_Calls.TerritoryManager, Sales_Calls.UserID, Users.UserID;
</cfquery>

<cfset myNewQuery = QueryNew("SaleDate, TerritoryManager, UserID, UserZone, rCount")>
<cfset newRow = QueryAddRow(MyNewQuery, #orders.RecordCount# + #sales_calls.recordCount#)>
<cfset counter = 0>
<cfoutput query="orders">
     <cfset counter = counter + 1>
     <cfset temp = QuerySetCell(myNewQuery, "SaleDate", orders.SaleDate, counter)>
     <cfset temp = QuerySetCell(myNewQuery, "TerritoryManager", orders.TerritoryManager, counter)>
     <cfset temp = QuerySetCell(myNewQuery, "UserID", orders.UserID, counter)>
  <cfset temp = QuerySetCell(myNewQuery, "UserZone", orders.UserZone, counter)>
  <cfset temp = QuerySetCell(myNewQuery, "rCount", orders.orders, counter)>
</cfoutput>

<cfoutput query="sales_calls">
     <cfset counter = counter + 1>
     <cfset temp = QuerySetCell(myNewQuery, "SaleDate", sales_calls.SaleDate, counter)>
     <cfset temp = QuerySetCell(myNewQuery, "TerritoryManager", sales_calls.TerritoryManager, counter)>
     <cfset temp = QuerySetCell(myNewQuery, "UserID", sales_calls.UserID, counter)>
  <cfset temp = QuerySetCell(myNewQuery, "UserZone", sales_calls.UserZone, counter)>
  <cfset temp = QuerySetCell(myNewQuery, "rCount2", sales_calls.calls, counter)>
</cfoutput>

<cfoutput query="MyNewQuery">
      #currentrow#) #SaleDate# - #TerritoryManager# - #UserID# - #UserZone# - #rCount# - #rCount2#<BR>
</cfoutput>

I can never get the result of #rCount2#, my output row displays sans '- #rCount2#' but cfdump times out before displaying anything. Can anyone point me in the right direction here...I need this to display like this;

Date	Territory Manager	Total Orders	Total Calls	Close %
9/15/2009	John Doe	5	3	60%

Any thoughts on performing the necessary calculation to achieve the Close % would help too.

[Dan_Bracuk]

That's a lot of code.  Looks like a case of possible overengineering.

Can you explain in 50 words or less what rcount2 is supposed to represent?

[Guest]

Yes, rCount2 would be the total number of calls entered in the database for the date range given. And it very well may be over engineering...I tend to do that a lot. Any help or advice would be greatly appreciated.

[Dan_Bracuk]

arraysum(orders["orders"]) gives you the number of orders.  Does that get you started?

[Guest]

I hope so. I'm not familiar with it or its usage but I guess it's time to learn, huh? Would I be replacing the rCount's with this?