Hi

I am using LLBLGen 4.2, Self servicing.

I have a table with the following attributes:

Id (integer) Code (int) Description (varchar 60) DateTimeBegin (date time)

DateTimeBegin is a date that can be in the past or future. I want to list the combinations of the Code and Descriptions that are currently valid. E.g. by filtering on MAX(DateTimeBegin) for a Code where DateTimeBegin <= DateTime.Now

And then I also want a lists of combinations that are not currently valid because they have a DatTimeBegin that lies in the past and is not in the first list.

I could try to solve this using a typed view, but are there other ways?

E.g.

select Id, DateTimeBegin, Code, Description from TCS_Recipe as T1 inner join (select Code as MaxCode, MAX(DateTimeBegin) as MaxDateTimeBegin from TCS_Recipe where DateTimeBegin > GetDate() group by Code) as T2 ON T1.Code = T2.MaxCode and T1.DateTimeBegin = T2.MaxDateTimeBegin

The query you posted suggest a Derived Table. Please the documentation for explanation and examples.

I also think that you could reformulate the query to use an "IN" clause, instead of a join. Please try to formulate what you want using the LLBLGen Pro API and tell us if you need further help with some part.

I solved it defining 3 views in SQL Server.

The SQL for the view that shows the actual combinations is

SELECT T1.Id, T1.DateTimeBegin, T1.Code, T1.Description FROM dbo.XXX_Table AS T1 INNER JOIN (SELECT Code AS MaxCode, MAX(DateTimeBegin) AS MaxDateTimeBegin FROM dbo.XXX_Table WHERE (DateTimeBegin <= GETDATE()) GROUP BY Code) AS T2 ON T1.Code = T2.MaxCode AND T1.DateTimeBegin = T2.MaxDateTimeBegin

Is it ugly or inefficient? What is the best practice?

that's perfectly fine. TO be able to do anything with MAX, you first have to calculate it. Your join does that once, an alternative would be to do it in the where clause which would do it per row so this is more efficient.

I try to keep complexity out of the database. Can this be done in an efficient way in LLBLGen, e.g. by using types lists?

Yes, you can reproduce that query on LLBLGen. Although TypedLists are more straight-forward combinations that you can design on LLBLGen Designer. In your query, a DynamicList is more appropriate (Ref...).

To write a DynamicList you can use either LLBLGen API (the link I posted above), QuerySpec, Linq2LLBL.

You also could grab your view in your DB, and map it as a TypedView in LLBLGen Designer, or even map it to an entity. See the documentation for details.

I should have mentioned that the 3 views I created are mapped to typed views.