SQL Expression, how do I display 1 field and limit the max date
Posted on 2014-03-28
I am trying to display in a SQL expression field that will display a value. Because this value can be recorded multiple times I will need to limit the data to pull only the value from the last date it was recorded.
The stored data looks like this:
ID Recorded Time Value
1234 1/1/2014 1
1234 2/15/2014 5
1234 3/1/2014 3
I want to display the value of "3" from the last line on the report. I do not need to show the date.
I have tried to add a MAX(Y.RECORDED_TIME) to the WHERE clause but I'm not sure how to do it correctly.
This is what I have used to pull the value(doesn't work if there is more than 1 value because of the multiple response).
INNER JOIN IP_FLWSHT_REC Z ON X.INPATIENT_DATA_ID = Z.INPATIENT_DATA_ID
INNER JOIN IP_FLWSHT_MEAS Y ON Z.FSD_ID = Y.FSD_ID
X.INPATIENT_DATA_ID = "PAT_ENC"."INPATIENT_DATA_ID" AND
Y.FLO_MEAS_ID = '98765'