Get distinct count in a group to take account of only most recent record

Question

I am looking at summarising the number of drugs prescribed, per patient for a patient's most recent stay in hospital. In my group output I currently have:

Patient ref 1234,Total Drugs 9
Patient ref 3456, Total Drugs 12

However in this example lets say the total number of drugs in the most recent stay in hospital (as opposed to all stays) should actually be:

Patient ref 1234,Total Drugs 6
Patient ref 3456, Total Drugs 10

When I put all the drugs and other information in "Details" I have been able to restrict successfully to display only drugs in most recent stay in hospital by using Selection Formulas, Group Selection:

({patient_stay.lnkstay} = Maximum({patient_stay.lnkstay},{patient_number}))

NB If a patient has had three stays in hospital, the field patient_stay.lnkstay would display 1.00, 2.00, 3.00 with 3.00 being the most recent. However in my group results:

Patient ref 1234,Total Drugs 9
Patient ref 3456, Total Drugs 12

I cannot seem to get the DistinctCount (currently used to produce Total drugs 9 and 12) to only count for the most recent stay for each patient. So I need a way to DistinctCount only on those drugs associated with the most recent stay for each patient (using maximum on field patient_stay.lnkstay).

Answer 1

You need to change a bit on your logic. Try this:

create second group with {patient_stay.lnkstay} and take summaries in group footer 2.

Now use below logic in supress of group header 2 and group footer 2

({patient_stay.lnkstay} = Maximum({patient_stay.lnkstay},{patient_number}))

Totally supress Details, Group Header1 and Group footer1