CODEWITHSUNDEEP
mysqlsql
ALFIE
ALFIE

Reputation: 31

SQL select most recent date for each record

I am trying to get results for the most recent dispensing date with reference to the specified dispensing_date. Why is my query not working?

select 
visits.patient_visit_id,pv.patient_id,pv.dispensing_date as dispensing_date,pv.dose,pv.quantity,pv.duration as days,dc.drug,r.regimen_code
FROM v_patient_visits pv

left join drugcode dc on drug_id = dc.id
left join regimen r on r.id = pv.current_regimen
left join patient_status ps on ps.id = pv.current_status

inner join 
(SELECT patient_visit_id, max(dispensing_date) as max_dispensing_date
 FROM `v_patient_visits`
 GROUP BY patient_id
) as visits 
ON visits.patient_visit_id = pv.patient_visit_id AND
   visits.max_dispensing_date = pv.dispensing_date

where 
pv.dispensing_date <= '2018-12-31'
and regimen_service_type  = 'service one'
and ps.Name = 'Status one'

The result picks the correct patient_id, dose, quantity and days but the dispensing date is not the most recent with reference to <='2018-12-31'

The v_patient_visits table looks like this:

patient_visit_id    patient_id  dispensing_date    dose    quantity    days    drug    current_regimen    current_status    regimen_service_type
48240   000001  2019-01-16    1OD    39    39    1    11    21    service one
48240   000001  2018-12-06    1OD    39    39    1    11    21    service one
48240   000001  2017-12-05    1OD    39    39    2    12    21    service one
48240   000001  2016-12-04    1OD    39    39    3    13    21    service one
48241   000002  2019-01-10    1BD    40    40    4    14    22    service two
48241   000002  2018-11-10    1BD    40    40    4    14    22    service two
48241   000002  2017-11-09    1BD    40    40    5    15    22    service two
48241   000002  2016-11-08    1BD    40    40    6    16    22    service two
48242   000003  2017-10-15    1OD    41    41    7    17    21    service one
48242   000003  2016-10-14    1OD    41    41    8    18    21    service one
48242   000003  2015-10-13    1OD    41    41    9    19    21    service one

It may have multiple entries for the same patient_id and I want it to pick the most recent dispensing date

The drugcode table looks like this:

id    drug
1   Drug one
2   Drug two
3   Drug three
4   Drug four

The regimen table looks like this:

id  regimen_code
11  Regimen one
12  Regimen two
13  Regimen three
14  Regimen four

The patient_status table looks like this:

id    Name
21  Status one
22  Status two
23  Status three
24  Status four

How I want the output to be like:

patient_visit_id    patient_id  dispensing_date    dose    quantity    days    drug    regimen_code
48240   000001  2018-12-06    1OD    39    39    Drug one    Regimen one
48242   000003  2017-10-15    1OD    41    41    Drug seven    Regimen seven

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

Close. You need to join the subquery on the date!

(SELECT patient_id, max(dispensing_date) as max_dispensing_date
 FROM `v_patient_visits`
 GROUP BY patient_id
) visits 
ON visits.patient_visit_id = pv.patient_visit_id AND
   visits.max_dispensing_date = pv.dispensing_date

Upvotes: 2

Related Questions