Reading your link now on how to post the data examples better

Correct, I only want the 2nd row based on the date value

So, right now I'm getting back:

Patient_Status ...

worked perfectly....thanks Ed!

Thanks! Do I need to set it up as a CTE? And it will be only one colon

Ok, it is a date time field type....and I show records for Dec 1 2015.....but, when I say that field = 12/01/2015 I get 0 results

It maybe a datetime....but, I'd like to take that field and run the prior days data. Could I do that with a field that displays data in that format?

Luis....your suggestion worked perfect when I inserted that case statement (Aliased 'Previous_Text5) into my test query:

SELECT case when Dyn_Assessments.field_name = '*Previous Treatment (Medication(s) patient has previously received for same or...

Thanks! For the help. I was thinking that I had to put the convert code at the end (ex. Convert(Varchar....101)...so that kept me from doing it. LOL

Luis...putting together the data example now.

Sean, I'm getting an invalid column error on 'GrantColumn' and 'PartDColumn'

Wouldn't your case statement error out do the columns in the Then portion containing columns that don't exist?

I'm thinking it's some kid of order logic. If the Payer is commercial then BINNO else if Payer = Grant then BINNO...etc

Sean, I want the data in a single column

For ex...patient 1234 has Commercial Insurance and BINNO 4321

patient 1234 has Part D Insurance and BINNO 789

...

I'm not explaining it clear enough. I want it to populate the same column. If it's Commercial give me that BINNO.....if the patient doesn't have Commercial....populate that column...