Guys

Hope someone could shed some light on a query i'd like to build using LLBLGEN.

Using LLBLGEN 2.6, Adapter, C#, SQL 2008, .NET 3.5

Tables:

PstFileUploadProgress (shortened) - PstFileEntryId int - PstFileUploadProgressStatus int - PstFileUploadProgressDateTime datetime

PstFileEntry (shortened) - PstFileEntryId int - OwnerEntryIdOverride int - Size bigint

Relation using PstFileEntryId, PstFileEntry is PrimaryKey Table.

PstFileUploadProgress Table has multiple entries for each PstFileEntryId with multiple DateTime's.

The goal is to retrieve all rows in PstFileUploadProgress together with the Size by joining the PstFileEntry table, and to only retrieve the "latest" entry (max(PstFileUploadProgressDateTime)) for each PstFileEntryId (group by), and only those who have got PstFileUploadProgressStatus = 1 (as the latest entry!).

We then just want the distinct count of OwnerEntryIdOverride as well as the SUM of the Size.

In SQL, this looks like:

SELECT 
  COUNT(DISTINCT(PstFileEntry.OwnerEntryIdOverride)) as [UserCount], 
  SUM(Size)/1024/1024 as FileSizeInMB
  FROM PstFileUploadProgress a 
  INNER JOIN PstFileEntry ON a.PstFileEntryId = PstFileEntry.PstFileEntryId, 
    (SELECT PstFileUploadProgress.PstFileEntryId, max(PstFileUploadProgressDateTime) as max_date 
    FROM PstFileUploadProgress
    GROUP BY PstFileUploadProgress.PstFileEntryId) b
    
  WHERE a.PstFileEntryId = b.PstFileEntryId 
  AND a.PstFileUploadProgressDateTime = b.max_date
  AND a.PstFileUploadProgressStatus = 1

As for LLBLGEN, I'm a little lost with the second "SELECT" part.

What I have so far is:

  var fields = new ResultsetFields(2);
  fields.DefineField(new EntityField2(PstFileEntryFields.OwnerEntryIdOverride), 0, "UserCount");
  fields.DefineField(new EntityField2("PstSize", (PstFileEntryFields.Size/1024/1024), AggregateFunction.Sum), 1);               
  fields[0].AggregateFunctionToApply = AggregateFunction.CountDistinct;
  fields[1].AggregateFunctionToApply = AggregateFunction.Sum;

  predicate.Relations.Add(PstFileUploadProgressEntity.Relations.PstFileEntryEntityUsingPstFileEntryId);

Unfortunately, that's all

Thanks folks, and Cheers Michel

(SELECT PstFileUploadProgress.PstFileEntryId, max(PstFileUploadProgressDateTime) as max_date FROM PstFileUploadProgress GROUP BY PstFileUploadProgress.PstFileEntryId) b

You can join to this select as atemp table, right?

Please check Derived tables and dynamic relations