sql server - Obtain records with multiple instances of field values -

i have patient database , trying extract list of patients have particular diagnosis , have specific procedure performed. problem want retrieve list of patients have procedure done @ least twice. here sample data.

table name = "clinicaldata"  patientid        diagnosis           procedure               proceduredate -------------------------------------------------------------------------- 1                laceration          stitches                2/12/2013 1                fracture            cast                    2/12/2013 1                fracture            cast                    2/13/2013 2                lung-cancer         chemotherapy            4/07/2013 2                lung-cancer         radiation               3/02/2013 2                liver-cancer        chemotherapy            6/03/2013 3                diabetes            hemoglobin-a1c-check    3/12/2013 3                diabetes            hemoglobin-a1c-check    7/11/2013  select     patientid,    min(proceduredate)      clinicaldata cd1       inner join clinicaldata cd2 on cd1.patientid = patientid          , diagnosis in ('laceration', 'lung-cancer','diabetes')    procedure in ('stitches', 'cast', 'hemoglobin-a1c-check') group    patientid 

what want obtain list of patients have have diagnosis of (laceration, lung-cancer, or diabetes) have had 1 of listed procedures ('stitches', 'cast', 'hemoglobin-a1c-check') @ least twice , earliest visit date. in above example obtain ...

patientid       proceduredate ------------------------------ 3               3/12/2013 

because interested in finding patients laceration, lung-cancer, , diabetes notice patient 1 not included since his/her diagnosis fracture. problem having how patients match diagnosis , procedures looking , have had given procedure done @ least twice. have tried doing self join , modifying where clause search second instance of procedure when try , run query keep going ... , going ... must have wrong. here sql tried.

select     patientid,    min(proceduredate)      clinicaldata cd1       inner join clinicaldata cd2 on cd1.patientid = cd2.patientid          , cd2.diagnosis in ('laceration', 'lung-cancer','diabetes')       inner join clinicaldata cd3 on cd1.patientid = cd3.patientid    cd1.procedure in ('stitches', 'cast', 'hemoglobin-a1c-check')    , cd3.procedure in ('stitches', 'cast', 'hemoglobin-a1c-check') group    patientid 

i tried put short example. in real life diagnosis , procedures represented codes , not hard coded in query. done via lookup tables, example . where procedure in (select procedure procedurelist)

select     patientid,    min(proceduredate)      clinicaldata cd1    [procedure] in ('stitches', 'cast', 'hemoglobin-a1c-check')          , diagnosis in ('laceration', 'lung-cancer','diabetes') group    patientid, diagnosis, [procedure] having count(*) > 1