sql - MSSQL find most recent measurement of most recent visit where person seen within the last year -

the generic example can think of people seeing doctor keeps trying lower patient's temperature i'd want see lowest reading.

patients.patient_id, patients.doc_id

visits.visit_id, visits.patient_id, visits.doc_id, visits.datetime

vitals.vitals_id, vitals.visit_id, vitals.patient_id, vitals.temp, vitals.datetime

doctor.doc_id, doctor.name

select patients.patient_id, last_visit.temp, last_visit.maxdate   patients   inner join doctor on patients.doc_id = doctor.doc_id   inner join (       select visits.patient_id, last_vitals.temp, max(visits.datetime) maxdate       visits       inner join (           select vitals.visit_id, vitals.temp, max(vitals.datetime) maxvitals         vitals         group vitals.visit_id, vitals.temp         )  last_vitals on visits.visit_id = last_vitals.visit_id     group visits.patient_id, visits.datetime       having visits.datetime >= dateadd(mm, -12, getdate())       ) last_visit on patient.patient_id = last_visit.patient_id    doctor.name = 'dr. jones'   

my attempts end returning many results attempt make. don't know if i'm supposed nest joins recent temperature recent visit? , if so, join visits first , vitals or vice versa? or approaching incorrectly altogether?

any fresh ideas appreciated.

i approach problem differently altogether. have 3 problems solve.

1. what top visit per person per doctor in last 12 months
2. what recent measurement per visit
3. only show results dr. jones primary care physician

the first 2 can solved view or cte , joined against. recommend separate views can evolve application logic (if definition of mostrecent whatever changes, update view). here cte example though see in 1 place:

old: http://sqlfiddle.com/#!3/3552e/8

new based on per patient per doctor: http://sqlfiddle.com/#!3/660be/3

with mostrecent_visits_per_patient_per_doctor (   select a.patientid,b.doctorid,max(b.visitdate) visitdate     patients     join visits b on a.patientid = b.patientid    b.visitdate >= dateadd(mm,-12,getdate())    group a.patientid,b.doctorid ), mostrecent_vitals_per_visit (   select a.visitid visitid,max(b.vitalsdate) topvitalsdate     visits     join vitals b on a.visitid = b.visitid    group a.visitid )  select a.patientid       ,b.visitid       ,b.visitdate       ,p_docs.docname primary_physician       ,v_docs.docname attending_physician       ,c.temp       ,c.vitalsdate   patients   join visits b on a.patientid = b.patientid   join vitals c on b.visitid = c.visitid   join doctors p_docs on a.doctorid = p_docs.doctorid   join doctors v_docs on b.doctorid = v_docs.doctorid   join mostrecent_visits_per_patient_per_doctor x         on a.patientid = x.patientid        , b.doctorid = x.doctorid        , b.visitdate = x.visitdate   join mostrecent_vitals_per_visit z         on c.visitid = z.visitid        , c.vitalsdate = z.topvitalsdate  --where p_docs.docname = 'dr. jones'