4 Replies Latest reply on Nov 1, 2009 5:15 PM by emartek1

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

    emartek1 Level 1

      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.