Skip to main content
November 1, 2009
Question

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

  • November 1, 2009
  • 1 reply
  • 623 views

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;

DateTerritory ManagerTotal OrdersTotal CallsClose %
9/15/2009John Doe53

60%


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

This topic has been closed for replies.

1 reply

Inspiring
November 2, 2009

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?

November 2, 2009

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.

Inspiring
November 2, 2009

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