Query of queries help - This isn't working correctly!
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.
