Hi, I'm having a hard time to figure out the following. Suppose you have a table like this:

Employee | Sales | Month

1 | 500 | 1 1 | 200 | 2 1 | 100 | 3 1 | 300 | 4 2 | 100 | 1 2 | 400 | 2

Well, now you want to have a result set that contains (1) the sum of sales, (2) the maximum months and (3) the amount of included months for (a) an employee X and (b) a certain amount of months Y.

Example 1: (a) employee X= 1, (b) months Y<= 3

(1) Sum of Sales | (2) Max. Months | (3) Included Months

800 | 4 | 3

In the first Y==3 months emloyee X==1 has made (1) 800$, which is the sum of (3) 3 months of selling. All in all he has been selling products since (2) 4 months.

Example 2: (a) employee X= 2, (b) months Y<= 3

(1) Sum of Sales | (2) Max. Months | (3) Included Months

500 | 2 | 2

In the first Y==3 months emloyee X==2 has made (1) 500$, which is the sum of (3) 2 months of selling. All in all he has been selling products since (2) 2 months.

My Problem: I'm not sure ho to solve this task with LLBL Gen.

First I tried to create a database view like

viewSales: 
SELECT Employee, SUM(Sales) AS sumSales, MAX(Months) AS maxMonths
FROM Sales
GROUP BY Employee

and then I hoped to be able to use an IPredicateExpression like

viewSalesCollection sales = new viewSalesCollection();
IPredicateExpression filter = new PredicateExpression();
filter.AddWithAnd(viewSalesFields.employee == x);
filter.AddWithAnd(viewSalesFields.months <= y);

If I set x=1 and y=3 as in example no. 1, then I receive the following result (which is not what I'm looking for, see column (2) in red):

(1) Sum of Sales | (2) Max. Months | (3) Exisiting Months

800 | 3 | 3

The problem is the "month filter". I can not limit the months to 3 on the one hand, and on the other hand expect a (2) max. amount of months of 4.

I'm sure there is a way to solve this whole thing with LLBL, the question is just: HOW?

Could anybody please help me out on this? Thank you so much! Ingmar

Hi Ingmar.

You can achieve that excluding the MAX(Months) form the query filter.. You can use ScalarQueryExpression.

The SQL you are looking for is something like:

SELECT 
    Employee, 
    SUM(Sales) AS sumSales, 
    COUNT(Months) as includedMonths
    (SELECT MAX(Months) AS maxMonths from Sales where Employee = x)

FROM Sales

WHERE 
    Employee = x and
    Months <= y

GROUP BY 
    Employee

And your code should look like:

// define fields
ResultsetFields fields = new ResultsetFields(4);
fields.DefineField(SalesFields.EmployeeId,0, "Employee");
fields.DefineField(SalesFields.Sales, 1,  "sumSales", AggregateFunction.Sum);
fields.DefineField(SalesFields.Months, 2, "includedMonths", AggregateFunction.Count);
fields.DefineField(SalesFields.Months, 3, "maxMonths");

// set scalar query expression for the last field
ScalarQueryExpression exp = new ScalarQueryExpression(SalesFields.Months.SetAggregateFunction(AggregateFunction.Max), SalesFields.Employee == x);
fields[3].ExpressionToApply = exp;

// group by employee
IGroupByCollection groupBy = new GroupByCollection();
groupBy.Add(fields[0]);

// filter by employee and months
IPredicateExpression filter = new PredicateExpression();
filter.Add(SalesFields.Employee == x);
filter.Add(SalesFields.Months <= y);

// lets retrieve the data!
DataTable result = new DataTable();

TypedListDAO dao = new TypedListDAO();
dao.GetMultiAsDataTable(fields, result, 0, null, filter, null, true, groupBy, null, 0, 0);

For more info read LLBLGenPro Help - Using generated code - Field expressions and aggregates.

No tested but should works! Good luck.

Daelmo,

wow, that looks...fantastic. See, I had this feeling that there are much more features in LLBL than I'm currently using, even then I expected.

So, I will try to experiment with your suggestions, and I'm sure it will help me. Often it's the first idea that is missing. But now that you showed me some piece of code (which I would never have thought of myself), I can work on that.

Thank you so much for your detailed answer! Good for me that there are people outside like you! I hope I may come back to you if something does not work out as expected?

Ingmar