Aggregate Query and Custom Fields

Bill Cunnien — Thu, 14 Aug 2008 10:19:22 GMT

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:

[codesnippet]
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
[/codesnippet]

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

RE: Aggregate Query and Custom Fields

Trent L. Taylor — Thu, 14 Aug 2008 10:19:22 GMT

LOL...we have all been there...even this morning :D Glad you got it working!

RE: Aggregate Query and Custom Fields

Bill Cunnien — Thu, 14 Aug 2008 10:11:36 GMT

:blush:

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!

:D
Bill

StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Aggregate Query and Custom Fields

Aggregate Query and Custom Fields

RE: Aggregate Query and Custom Fields

RE: Aggregate Query and Custom Fields