· May 12, 2021

Cache sql query

Hi ,

     I am trying to retrieve the contact type column data of a patient based on the recent date as shown below.

for instance

 contacttype      Datefrom

contact 1.       24/03/2020

contact 2.        20/05/2021

i need to retrieve the second contact2 based on the recent date.

i tried  like below but not working .

Case max(datefrom) >0 then contacttype

End as contacttype


Can anyone please tell me why this is not working.Any other options?



Product version: Caché 2015.1
Discussion (3)1
Log in or sign up to continue

I assume you have a PatientID so you might be able to select your records using an INNER JOIN in this way:

SELECT <.......your columns....,contacttype,....> 
FROM my.patient as pat
(select PatientID, MAX(DateFrom) as MaxDate FROM my.patient Group by PatientID) as max
pat.PatientID=max.PatientID AND pat.DateFrom=max.MaxDate
WHERE  <...whatever... >

so you get the records with the highest DateFrom by Patient