If you are using E10, maybe try posting the SQL Script that it is generating? We might be able to get a better picture
I am working on a query to summarize sales info per customer that purchased in the last seven days.
I am looking for something along these lines.
These would be the headers - CustID, #OfOrders, #OfOrdersEC, #OfOrdersInd, #OfOrdersLead
Here are the algorithms for each column.
CustID = Customer ID that were on a non-void order within the last seven days. (this one I have)
#OfOrders = For this CustID, count the number of orders
#OfOrdersEC = For this CustID, count the number of orders where OrderDtl.Character01 is not blank.
#OfOrdersInd = For this CustID, count the number of orders where OrderDtl.SalesCatID is not blank.
#OfOrdersLead = For this CustID, count the number of orders where OrderDtl.Character02 is not blank.
I envision the results to look something like this.
| CustID | #OfOrders | #OfOrdersEC | #OfOrdersInd | #OfOrdersLead |
| 16 | 20 | 5 | 4 | 2 |
| 14 | 5 | 2 | 0 | 0 |
| 19 | 7 | 7 | 4 | 1 |
I assume that I would have to create a sub-query in a calculated field but I am not sure. My efforts so far have not been successful.
Can you give me some suggestions?
You should be able to use the count function. If you already have your filters set up to find the customer and order lines in the time frame you want, you should be able to use count(desired value). Don't quote me on my context and I haven't plugged it in yet so I'm not 100% sure it will work, but generally you can use pretty much and standard SQL function in a query. One thing about grouping stuff is since Epicor has the built in group by check boxes you end up ignoring the select part of the function (make sure you set them though, or it will throw an error), and since you have the filters in the query builder you can ignore the WHERE function since you've already set it. See the link below to read a bit about the count function.
SQL: COUNT Function
SQL: COUNT Function
 | SQL: COUNT Function This SQL tutorial explains how to use the SQL COUNT function with syntax, examples, and practice exercises. The SQL COUNT function is used to count t... | |||
| Preview by Yahoo | ||||
Thanks for that.
I am trying to create a Calculated field using SQL statements but I am getting this error.
"OrderHed Company must be a quoted constant or an unabbreviated, unambiguous buffer/field reference for buffers known to query . (7328)"
I am not sure how to correct this or what exactly the problem is.