I have a CustomersBO that has several custom fields to reflect order totals for the typical calendar breakdowns - week-, month-, quarter-, and year-to-date. I have a stored procedure that summarizes these totals for me. I use that stored procedure to fill my BO. Here is a snippet of the sql script: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