I hope someone out there can help me out. I have two tables: Registration and Registration status. In the registration status we are storing the statuses of each registrant (new, onhold, registered, etc). Each status is given a date, for example:

Registration ID 1 may look like: 1/1/2005 11:40 PM - New 1/2/2005 12:30 PM - Onhold 1/3/2005 1:00 PM - Denied 1/4/2005 8:00 AM - New

So, the most recent record in our registration status table tells us the current status of each registrant.

I need to write a query using LLBL that lets me create an entity collection of Registrations for all statuses that are currently new. So my query look like this:

SELECT Registration.* 
FROM   Registration INNER JOIN
            RegistrationStatus ON Registration.RegistrationId =  RegistrationStatus.RegistrationId 
             INNER JOIN
                          (SELECT    RegistrationId, MAX(StatusDate) AS LastStatusDate
                            FROM          RegistrationStatus
                            GROUP BY RegistrationId) MaxReg ON Registration.RegistrationId = MaxReg.RegistrationId AND 
                      RegistrationStatus.StatusDate = MaxReg.LastStatusDate INNER JOIN
                      ProjectStatus ON dbo.HCRegistrationStatus.ProjectStatusId = ProjectStatus.ProjectStatusId
WHERE    (RegistrationStatus.ProjectStatus= 'New')

I want to use LLBL to create my RegistrationCollection without using a stored procedure or view. I don't know how to get LLBL to generate that INNER JOIN (SELECT MAX...GROUP BY) portion.

I'm stumped...

Thanks for the help,

• Chad

This thread may help you out: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4792

Please re-write the query, you can't join with a select result at the moment.