Hi guys, sorry this is washing over my head again!!! how can i write this select statement is LLBL

What it does is allow a manager to see how much users have spent in a certain month and year.

SELECT   [user].username, SUM(Costs.costamount) AS TotalSpend
FROM         Costs INNER JOIN
                      [user] ON Costs.owner = [user].userkey
WHERE CONVERT(VARCHAR(7),Costs.CostDate, 20) = @Date --'2007-03'
GROUP BY [user].username

Thanks Jason

Hi Jason, your code should seem to this. (_Ref: Using Generated Code - SelfServicing - Using TypedViews, TypedLists and DynamicLists - Using dynamic lists_)

            // fields to retrieve
            ResultsetFields fields = new ResultsetFields(2);
            fields.DefineField(UserFields.UserName, 0, "UserName", "User");
            fields.DefineField(CostsFields.CostAmount, 1, "TotalAmount", "Costs", AggregateFunction.Sum);

            // specify the filter 
            IPredicateExpression filter = new PredicateExpression();

            // specify relation
            IRelationCollection relations = new RelationCollection();
            relations.Add(UserEntity.Relations.CostsEntityUsingUserName, "User", "Costs", JoinHint.None);
            
            // group the result
            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields[0]);

            // retrive resulset
            DataTable dynamicList = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dynamicList, 0, null, null, relations, true, groupByClause, null, 0, 0);

And for the datepart filter, please read this thread: [http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3829](http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3829)

Good luck

daelmo wrote:

And for the datepart filter, please read this thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3829

My apologies about the thread I suggested. It's an old one. Now you can use DBFuntionCall's.

You could use something like this:

// create the fields you'll use at your predicate
ResultsetFields filteredFields = new ResultsetFields(2);
filteredFields.DefineField(CostsFields.Date, 0, "Year", "Costs");
filteredFields.DefineField(CostsFields.Date, 1, "Month", "Costs");

// setting the YEAR and MONTH functions
filteredFields[0].ExpressionToApply = new DbFunctionCall("YEAR", new object[] { CostsFields.Date.SetObjectAlias("Costs") });
filteredFields[1].ExpressionToApply = new DbFunctionCall("MONTH", new object[] { CostsFields.Date.SetObjectAlias("Costs") });

// filter example for filtering 1996-08
filter.Add( new FieldCompareValuePredicate(filteredFields[0], ComparisonOperator.Equal, 1996));
filter.AddWithAnd( new FieldCompareValuePredicate(filteredFields[1], ComparisonOperator.Equal, 8));

// ... include predicate in dynamic list retrieve call

Hope helpful