SQL MIN() IF enrollDate lapseDate

See more:

SQL

Program enrollment creates a challenge in our system. When a client is shifted from one staff to another, the system also un-enrolls the client from their program and re-enrolls the client into the program that same day.

OBJECTIVE: My query should identify the first enrollment date "p_effdt" (prior to any re-enrollments). But, I do not want the MIN(p_effdt) because that might not reflect any actual lapses of enrollment.

EXAMPLE: Client Adam enrolls on 2/1/2013. On 4/1/2013 he switches to a different staff so our system will un-enroll and re-enroll Adam on 4/1/2013. The date I want to report for Adam is 2/1/2013. Client Eve enrolls on 2/1/2013 and then lapses on 5/1/2013 and re-enrolls on 7/1/2013. The date I want to report for Eve is 7/1/2013.

<pre>SELECT client.c_id, c_ln + ', ' + c_fn AS 'Client', program.p_id, MIN(p_effdt) --that is wrong!
FROM client INNER JOIN program ON client.c_id = program.c_id
WHERE program.p_effdt BETWEEN '01.01.2013' AND '12.31.2013'
AND program.p_id = '1234'
GROUP BY client.c_id, c_ln, c_fn, program.p_id, p_effdt
ORDER BY client.c_id   

The logic would be something like: if p_lpsdt = p_effdt then go to prior p_effdt and ask if p_lpsdt = p_effdt again ... Should I put that in the SELECT or use ROWCOUNT() or ...?

Now I am trying:

<pre>WHERE ((program.p_effdt BETWEEN '01.01.2013' AND '12.31.2013') AND ((p_effdt <> p_lapdt) OR p_lapdt IS NULL))

Thank you for your help.