4 Replies Latest reply on Jan 8, 2010 5:43 AM by Dan Bracuk

    Need to specify date range for query result.

    emartek1 Level 1

      Below is my query. The query as is is working fine. The columns 'totalCalls' , 'totalOrders' and 'totalSCs' are all stored by date. I have created a form where the user can specify a start and end date. How would I change this query to report within those specified dates.

       

      <cfquery name="qZVPData_Western" datasource="xxxxxx">
      SELECT UserID,
             TMName,
          UserZone,
          AVG(WeekCallGoal) AS WCG,
          AVG(QTCallGoal) AS QTCG,
                (SELECT COUNT(*)
                 FROM Sales_Calls
                 WHERE Sales_Calls.UserID = u.UserID) as totalCalls,
          (SELECT COUNT(*)
           FROM Orders
           WHERE Orders.UserID = u.UserID) as totalOrders,
          (SELECT SUM(Quantity)
           FROM ProductOrders PO
           WHERE PO.UserID = u.UserID AND PO.NewExisting = 1) as newItems,
          (SELECT SUM(NewExisting)
           FROM  ProductOrders PO_
           WHERE PO_.UserID = u.UserID) as totalNew,
          (SELECT COUNT(ServiceCall_ID)
           FROM  ServiceCalls SC
           WHERE SC.UserID = u.UserID) as totalSCs,
          (SELECT COUNT(UserID)
           FROM  TMStatusLog TSL
           WHERE TSL.UserID = u.UserID AND TSL.Status = 'Vacation') as TSLdays1,
          (SELECT COUNT(UserID)
           FROM  TMStatusLog TSL
           WHERE TSL.UserID = u.UserID AND TSL.Status = 'TradeShow') as TSLdays2,
          (SELECT COUNT(UserID)
           FROM  TMStatusLog TSL
           WHERE TSL.UserID = u.UserID AND TSL.Status = 'Admin Day') as TSLdays3,  
          SUM(TSLdays1)+(TSLdays2)+(TSLdays3) AS TSLdays,   
          SUM(totalOrders)/(totalCalls) AS closePerc,
          SUM(totalOrders)/(totalCalls) - (.30) AS GRV,
          SUM(totalSCs)+(totalCalls)-(QTCG) AS PerVar,
         (SUM(totalSCs) + totalCalls + (TSLdays*WCG/5))/QTCG AS PerCalls
      FROM Users u
      WHERE UserZone = 'Western'
      GROUP BY UserZone, UserID, TMName
      </cfquery>

       

      I figured I could add this to the columns WHERE statements;

       

      'AND Column BETWEEN #FORM.Start# AND #FORM.End#' but this isn't working.

       

      Any ideas???