views:

29

answers:

0

I'm rather new to coding so I'm sure this is fairly easy, but I have not been able to figure this out. I'm building a report in reporting serivces, sql 2005. I have 4 tables: people, event log, program enrollment and program info. I have clients that could be enrolled in numerous programs. I need to be able to find a client's first enrollment date for their enrollment into their first program. This is what I have so far:

SELECT     people.last_name, people.first_name, MIN(event_log.actual_date) AS 'First Enrollment Date', people.people_id
FROM         program_enrollment INNER JOIN
                      program_info ON program_enrollment.program_info_id = program_info.program_info_id INNER JOIN
                      event_log ON program_info.program_info_id = event_log.program_providing_service AND 
                      program_enrollment.program_enrollment_id = event_log.event_log_id RIGHT OUTER JOIN
                      people ON event_log.people_id = people.people_id
WHERE     (event_log.actual_date IN
                          (SELECT     MAX(actual_date) AS Expr1
                            FROM          event_log AS event_log_1
                            GROUP BY people_id))
GROUP BY people.last_name, people.first_name, people.people_id

This may not be correct or the most efficient way to do this, but it does give me the client name and their first enrollment date, so my result will be:

Last Name      First Name         First Enrollment Date

Jones          John               2/1/09
Smith          Sarah              8/21/08
Johnson        Sam                6/6/09

However when I try to add the program name to the first select statement I then get multiple programs with multiple enrollment dates. I know it's probably just a matter of adding an AND statement somewhere but haven't been able to work this out. Any help would be greatly appreciated.