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.