Need to specify date range for query result.

[Guest]

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???

[-__cfSearching__-]

'AND Column BETWEEN #FORM.Start# AND #FORM.End#' but this

isn't working.

Any ideas???

Not without a clarification of "isn't working" or seeing an error message.

Also, that is a lot of subqueries, which do not always perform well with large tables.

[Guest]

The error message I receive is 'too few parameters expected 1'. I know there are a lot of subqueries with this but aside from the date issue the query is running correctly.

[Adam Cameron.]

What is the SQL being generated by your <cfquery> contents?  Is it valid SQL?  This is always the first thing to check when you get SQL errors back from the DB... check what you're sending to the DB.

Second: don't hard-code dynamic values into your SQL string, pass them as parameters.

Re all the subqueries: it runs fine in dev. Have you tried to load test it?  If poss move your subqueries to the FROM statement, as then they're only run once per recordset. As opposed to once per row of the result set, when the subqueries are in the SELECT or WHERE statement.

--

Adam

[Dan_Bracuk]

I see two issues.

First, your form variables are strings, not date objects.  Sometimes you can get away with that but not always.  lsparsedatetime() is available to convert strings to dates.

Second, if your datatype is date and time, using "between" can cause you to miss records.  A better approach is:

where datefield >= date1

and datefield < date2