Min date

Transact-SQL
1

with flag as
(
select distinct patient_fyi_flags.PATIENT_ID,patient_fyi_flags.ACCT_NOTE_INSTANT
from patient_fyi_flags
where patient_fyi_flags.PAT_FLAG_TYPE_C = '1143'
and patient_fyi_flags.patient_id = '123456789'
)
select distinct pat_enc.pat_id,flag.ACCT_NOTE_INSTANT,pat_enc.contact_date
from flag
inner join pat_enc on flag.patient_id = pat_enc.pat_id
where pat_enc.pat_id = '075329162'
and flag.ACCT_NOTE_INSTANT <= pat_enc.contact_date

I get:
id date contact
123456789 07/02/2015 08:01:00 08/18/2015 00:00:00
123456789 07/02/2015 08:01:00 07/28/2015 00:00:00
123456789 07/02/2015 08:01:00 07/21/2015 00:00:00
123456789 07/02/2015 08:01:00 08/24/2015 00:00:00

How do I find the 7/21/2015 contact_date only?

2

somewhat difficult to follow without more background info, but maybe this:

with flag as
(
select distinct patient_fyi_flags.PATIENT_ID,patient_fyi_flags.ACCT_NOTE_INSTANT
from patient_fyi_flags
where patient_fyi_flags.PAT_FLAG_TYPE_C = '1143'
and patient_fyi_flags.patient_id = '123456789'
)
select pat_enc2.pat_id,flag.ACCT_NOTE_INSTANT,pat_enc2.contact_date
from flag
cross apply (
    select top (1) *
    from pat_enc
    where 
        --pat_enc.pat_id = '075329162' and --seems impossible, can't match flag.patient_id = '123456789'
        flag.patient_id = pat_enc.pat_id and
        flag.ACCT_NOTE_INSTANT <= pat_enc.contact_date
    order by pat_enc.contact_date
) as flag2