SELECT    
 Customers.*,
 (
 SELECT
  SUM(SalesOrderDetails.quantity * SalesOrderDetails.unitprice) AS wtdorders
 FROM
  SalesOrderDetails
  LEFT OUTER JOIN SalesOrderMaster ON SalesOrderMaster.orderindex = SalesOrderDetails.orderindex 
 WHERE
  (SalesOrderMaster.divisionindex = @div)
  AND SalesOrderMaster.custindex = Customers.CustIndex
  AND (SalesOrderMaster.orderdate BETWEEN @start_week AND @enddate)
  AS WTDOrders,
 ...
 ... other lines for mtd, qtd, and ytd here
 ...
FROM        
 CustomerMaster Customers
WHERE
 Customers.Status = 1
 AND Customers.inactive = 0

The line I marked in red is my suspected line.  The query runs, but I am getting a null value for every customer (or zero, if utilize the ISNULL function), no matter what dates or division I use.  The subquery works great on its own when I use a specific customer index.  I thought that I had used this kind of approach to retrieving sum values before, but for some reason I cannot figure out why this does not work.  Can anyone point out where my error may be in the query?

Thanks!
Bill

After wrestling too long with this query, I decided to start whacking every criteria and get it to work without any limitations (date, division or otherwise).  In the end, I discovered that my statusindex was keyed in wrong (not 1, but 3).  The query works and it works like a charm!!  It all comes down to the "good data in, good data out" principle.

Sorry to bother y'all with this one.  Have a great day!


Bill