This is what i am trying to use at the moment It doesnt work fields.DefineField(new EntityField2("CurrentSales", new ScalarQueryExpression( GroupStoreOrderRequestFields. GroupStoreOrderRequestId.SetAggregateFunction (AggregateFunction.Count), (GroupStoreOrderRequestFields.DateCreated >= DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd") + " 00:00:00.001") && GroupStoreOrderRequestFields.DateCreated <= DateTime.Parse(DateTime.Now.ToString("yyyy/MM/dd") + " 59:59:59.001")))), "CurrentSales");

All i want to do is define a field that counts the rows for the rows that datecreated is between startdate and enddate. Is this possible. Thanks

Would you please post the target SQL query?

Walaa wrote:

Would you please post the target SQL query?

SELECT COUNT([AxxessRad].[dbo].[GroupStoreOrderRequest].[GroupStoreOrderRequestId]) AS [GroupStoreOrderRequestId] FROM [AxxessRad].[dbo].[GroupStoreOrderRequest]
WHERE ( [AxxessRad].[dbo].[GroupStoreOrderRequest].[DateCreated] between date1 and date2)) AS [Request],

This was created by llblgen for a field with one where (eg where code = '01') ,I have edited it (eg where date between date1 and date2) .

SELECT COUNT(GroupStoreOrderRequest.GroupStoreOrderRequestId) AS GroupStoreOrderRequestId FROM GroupStoreOrderRequest WHERE ( GroupStoreOrderRequest.DateCreated between date1 and date2)) AS Request,

Just use the following code:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(GroupStoreOrderRequest.GroupStoreOrderRequestId, 0);
fields[0].AggregateFunctionToApply = AggregateFunction.CountDistinct;

And then add a filter to fetch method for the between predicate.

Walaa wrote:

SELECT COUNT(GroupStoreOrderRequest.GroupStoreOrderRequestId) AS GroupStoreOrderRequestId FROM GroupStoreOrderRequest WHERE ( GroupStoreOrderRequest.DateCreated between date1 and date2)) AS Request,

Just use the following code:

ResultsetFields fields = new ResultsetFields(1);
fields.DefineField(GroupStoreOrderRequest.GroupStoreOrderRequestId, 0);
fields[0].AggregateFunctionToApply = AggregateFunction.CountDistinct;

And then add a filter to fetch method for the between predicate.

This is what i am wanting. That sql was just for one column. I thought that one could speacify a expression per column

select product.name,groupstorerequest.datecreated as currentsales,space(20) as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date and date union select product.name,space(20) as currentsales,groupstorerequest.datecreated as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date2 and date2

This is what i am wanting. That sql was just for one column. I thought that one could speacify a expression per column

You can specify an expression per column/field. Using the ExpressionToApply property.

select product.name,groupstorerequest.datecreated as currentsales,space(20) as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date and date union select product.name,space(20) as currentsales,groupstorerequest.datecreated as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date2 and date2

It's not clear what's going wrong now with this code, so please explain. Btw, we don't support Union.

Walaa wrote:

This is what i am wanting. That sql was just for one column. I thought that one could speacify a expression per column

You can specify an expression per column/field. Using the ExpressionToApply property.

select product.name,groupstorerequest.datecreated as currentsales,space(20) as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date and date union select product.name,space(20) as currentsales,groupstorerequest.datecreated as previoussales from groupstoreorder inner join groupstorerequest on groupstorerequest.groupstoreorderid = groupstoreorder.groupstoreorderid inner join product on groupstorerequest.productid = product.productid where datecreated between date2 and date2

It's not clear what's going wrong now with this code, so please explain. Btw, we don't support Union.

Here is the answer.

public DataTable GetGroupStoreOrderActivityInfoDataTable(Guid? pGroupStoreId) { DataTable dataTable = new DataTable();

        this.LLBLGenPattern.PerformTryCatch(
            delegate(IDataAccessAdapter adapter)
            {
                //  bucket
                RelationPredicateBucket bucket = new RelationPredicateBucket();

                //  relations
                bucket.Relations.Add(
                    GroupStoreOrderEntity.Relations.GroupStoreOrderRequestEntityUsingGroupStoreOrderId,
                    JoinHint.Left);
                bucket.Relations.Add(GroupStoreOrderRequestEntity.Relations.ProductEntityUsingProductId,
                                     JoinHint.Left);
                bucket.Relations.Add(
                    GroupStoreOrderEntity.Relations.GroupStoreOrderActivationEntityUsingGroupStoreOrderId,
                    JoinHint.Left);

                //  filters
                bucket.PredicateExpression.Add(GroupStoreOrderFields.IsActive == true);
                if (pGroupStoreId != Guid.Empty)
                    bucket.PredicateExpression.Add(GroupStoreOrderFields.GroupStoreId == pGroupStoreId);

                //  sort


                //  fields
                ResultsetFieldsHelper fields = new ResultsetFieldsHelper();
                fields.DefineField(GroupStoreOrderFields.GroupStoreId);             
                fields.DefineField(GroupStoreOrderRequestFields.ProductId);
                fields.DefineField(ProductFields.Name);

** IPredicateExpression Current = new PredicateExpression(); Current.Add(GroupStoreOrderRequestFields.DateCreated >= DateTime.Parse(DateTime.Now.ToString("yyyy/MM") + "/01 00:00:00.001")); Current.AddWithAnd(GroupStoreOrderRequestFields.DateCreated <= DateTime.Parse(DateTime.Now.AddMonths(1).ToString("yyyy/MM") + "/01 23:59:59.001").AddDays(-1));**

                fields.DefineField(new EntityField2("Current",
                                        new ScalarQueryExpression(
                                            GroupStoreOrderRequestFields.DateCreated.SetAggregateFunction                                               
                                                (AggregateFunction.Count),
                                            (Current))), "Current");

            **  IPredicateExpression Previous = new PredicateExpression();
                Previous.Add(GroupStoreOrderRequestFields.DateCreated >= DateTime.Parse(DateTime.Now.AddMonths(-1).ToString("yyyy/MM") + "/01 00:00:00.001"));
                Previous.AddWithAnd(GroupStoreOrderRequestFields.DateCreated <= DateTime.Parse(DateTime.Now.ToString("yyyy/MM") + "/01 23:59:59.001").AddDays(-1));**

                fields.DefineField(new EntityField2("Previous",
                                        new ScalarQueryExpression(
                                            GroupStoreOrderRequestFields.DateCreated.SetAggregateFunction
                                                (AggregateFunction.Count),
                                            (Previous))), "Previous");



                //GROUP BY
                IGroupByCollection groupByClause = new GroupByCollection();
                groupByClause.Add(GroupStoreOrderFields.GroupStoreId);              

                groupByClause.Add(GroupStoreOrderRequestFields.ProductId);
                groupByClause.Add(ProductFields.Name);              



                //HAVING
                //groupByClause.HavingClause = new PredicateExpression(
                //  GroupStoreOrderRequestFields.Quantity > 10
                //  );

                adapter.FetchTypedList(fields.ToResultsetFields(), dataTable, bucket, 0, null, false, groupByClause);

            }
        );
        return dataTable;
    }

Sorry, is this the final solution to your issue, or something else that we need to investigate...?

Matt

MTrinder wrote:

Sorry, is this the final solution to your issue, or something else that we need to investigate...?

Matt

This is the solution. Thanks for your help. Not sure how to select that the question has been answered