I have a table with a datefield in it.

create TABLE FTAF(FtafID int IDENTITY(1,1) NOT NULL, SentDate datetime) 

I wish to do this query.

select count(sentdate) as count, max(sentdate) as date,
        datename(month,sentdate) as month, datename(year,sentdate) as year 
        from ftaf f     
        group by datename(month,sentdate), datename(year,sentdate)
        order by date asc

My LLBLGEN code looks like this. (I'm using a custom FunctionExpression that lets me do the dateName function) (Self Servicing model)

ResultsetFields fields = new ResultsetFields(3);
            fields.DefineField(FtafFieldIndex.SentDate, 0, "count",string.Empty,AggregateFunction.Count);
            fields.DefineField(FtafFieldIndex.SentDate, 1, "month");
            fields.DefineField(FtafFieldIndex.SentDate, 2, "year");

            fields["month"].ExpressionToApply = new FunctionExpression(EntityFieldFactory.Create(FtafFieldIndex.SentDate), "datename", "month,");
            fields["year"].ExpressionToApply = new FunctionExpression(EntityFieldFactory.Create(FtafFieldIndex.SentDate), "datename", "year,");

            IGroupByCollection groupByClause = new GroupByCollection();
            groupByClause.Add(fields["month"]);
            groupByClause.Add(fields["year"]);
            DataTable dt = new DataTable();
            TypedListDAO dao = new TypedListDAO();
            dao.GetMultiAsDataTable(fields, dt, 0, null, null, null, false, groupByClause, null, 0, 0);
            this.grdStats.DataSource = dt;
            this.grdStats.DataBind();

LLBLGEN gives me this

SELECT COUNT([dbo].[FTAF].[SentDate]) AS [count], 
    datename(month,[dbo].[FTAF].[SentDate]) AS [month], 
    datename(year,[dbo].[FTAF].[SentDate]) AS [year] 
    FROM [dbo].[FTAF]  
    GROUP BY [dbo].[FTAF].[SentDate],[dbo].[FTAF].[SentDate]

The problem is with the Group By. I need to get this

group by datename(month,sentdate), datename(year,sentdate) 

but LLBLGEN gives me this.

GROUP BY [dbo].[FTAF].[SentDate], [dbo].[FTAF].[SentDate]

Obviously, I need to add the expression as the group by, but I'm not sure how to go about this... Anyone have any thoughts?

Well, my workaround for this was just to do a view on the table, and filter against that. Still like to know if the other way is supported in LLBLGEN though.

It's not possible at the moment, as the GROUP BY clause is emitted in the DQE and not obeys any expression. For v2 this is scheduled to be changed, if possible (there are some problems with some db's with this, but we hope to find a solution for this).

I can also rewrite my query like this, but I'm not sure if this is supported either. I know you can do a sub select in a where, but not sure about derived tables.

select count(sentdate), month, year from
(
select sentdate,
    datename(month,sentdate) as month,
    datename(year,sentdate) as year
    from ftaf
) d
    group by  month, year

Derived tables also aren't supported.

Otis wrote:

Derived tables also aren't supported.

You're killing me here...

ecirpnaes wrote:

Otis wrote:

Derived tables also aren't supported.

You're killing me here...

I'm sorry, but these aren't supported... but that's often not a problem as you can write them as a subquery in the where. Though in your case, that's of course not going to help...