Deepak...this almost works.  What I should have shown in my example is that the client could have anywhere from 1 to N enrollments.  Thoughts?

I should have made client 456 have 3 different enrollments to make the point. 

Let me know if you have ideas.  Thanks.

I can't seem to repost the table, but if you simply add the following record to the example table above...that would do it:

ClientID = 456

ProgramID = 6

EnrollDate = 7/1/2016

DisenrollDate = 12/31/2016

Maybe, instead of the static "2" you used...that can be replaced by a function that counts the number or rows for each fixed ClientID???

Would that work?  What would that look like in your solution?

I attempted it with a little more sample data.  Why does ClientID 999 not show the right value?  Because DisenrollDate is NOT NULL...it should say "Discharged".  Also, stupid question, but how to i make EnrollDate and DisenrollDate show the actual date and not just the year?

Deepak...it seems to work.  I am staring at this and have no idea why it works though.

Thank you so much!  But why does that work?

I just realized why!  It's based on the alphabet of the first letter of the words Enrolled or Discharged. 

That is Brilliant!  Thank you