Hi,

I am using SQL Server 200 and LLBLGen 2.0 in my .NET 1.1 application and I am using adapter.

I have a simple table called Activity that holds one row for each activity and has a column called ActivityDate which holds a date and time as to when the activity takes place, plus a column called ActivityCount that holds the count of the activity.

I am trying to get the sum of ActivityCount for a specific day. In SQL Server I need to use the following query to strip out the time element of the ActivityDate field and thus get the values I want

SELECT   CONVERT(DATETIME, CONVERT(varchar, ActivityDate, 106), 102) AS ActivityDate, SUM(ActivityCount) AS ActivityCount
FROM         Activity
GROUP BY CONVERT(DATETIME, CONVERT(varchar, ActivityDate, 106), 102)

Obviously if I simply group by the ActivityDate column then it groups not only by date but by time as well - not what I want.

I am trying to replicate this as a dynamic list in LLBL so I have

ResultsetFields myFields = new ResultsetFields(2);
myFields.DefineField(ActivityFieldIndex.ActivityDate, 0, "ActivityDate");
myFields.DefineField(ActivityFieldIndex.ActivityCount, 1, "Count","Activity",AggregateFunction.Sum);
IRelationPredicateBucket myBucket = new RelationPredicateBucket();  

IGroupByCollection myGroupByClause = new GroupByCollection();
myGroupByClause.Add(myFields[0]);

DataTable myDynamicList = new DataTable();
myAdapter.FetchTypedList(myFields, myDynamicList, myBucket, 0, null, true, myGroupByClause);

return myDynamicList;

How do I change the definition for myFields[0] so that I can incorporate the expression CONVERT(DATETIME, CONVERT(varchar, ActivityDate, 106), 102) as the column expression

many thanks in advance

Huw

Try using a DBFunctionCall to the DATEPART function. Check the examples found in the manual: "Using the generated code -> Calling a database function"

(EDIT) Tip:

I am trying to get the sum of ActivityCount for a specific day.

If you are trying to get the sum of a column for a specific day rather than for each day. you may use the following query.

SELECT SUM(ActivityCount)
FROM
WHERE DATEPART(day,  ActivityDate) = 'the specific day value'

You can use multiple DATEPART functions to compare the month and the year too. You can use DATEONLY and DATE Also a DATEDIFF can be used.

You are correct, actually what I wanted was just the result for a particular day, so I can drop the ActivityDate column from my dynamic list and add a predicate expression to limit the result to the day I want... that way I get the ActivityCount just for the day.

Problem solved thanks

Regards

Huw

P.S. The Calling a databse function help also looks useful... sorry I did not spot that... but I will take a look as it may be useful in the future