asked on

SQL Expression, how do I display 1 field and limit the max date

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).

SELECT DISTINCT
Y.MEAS_VALUE
FROM
IP_DATA_STORE X
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

WHERE
X.INPATIENT_DATA_ID = "PAT_ENC"."INPATIENT_DATA_ID" AND
Y.FLO_MEAS_ID = '98765'

)

edtechdba

How about using a sub-query approach? Please see below.
- I couldn't figure out the exact table.column name of the Recorded Time, replaced in this query below with [RecordedTime].

SELECT MEAS_VALUE
FROM IP_DATA_STORE X                 
	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 
WHERE [RecordedTime] =
	(
		SELECT MAX([RecordedTime])
		FROM IP_DATA_STORE X1                 
			INNER JOIN IP_FLWSHT_REC Z1 
				ON X1.INPATIENT_DATA_ID = Z1.INPATIENT_DATA_ID
			INNER JOIN IP_FLWSHT_MEAS Y1 
				ON Z1.FSD_ID = Y1.FSD_ID 
			WHERE X1.INPATIENT_DATA_ID  = "PAT_ENC"."INPATIENT_DATA_ID" 
				AND Y1.FLO_MEAS_ID = Y.FLO_MEAS_ID
			GROUP BY Y1.FLO_MEAS_ID -- Join sub-query ID with main query ID
	)

Open in new window

jul_to_cool

ASKER

I'm still getting the error since there is more than one value stored.

The RECORDED_TIME field is in IP_FLWSHT_MEAS.

edtechdba

How about within the sub query using TOP 1 and order by the recorded date in DESC order?

SELECT TOP 1 RECORDED_TIME
...
ORDER BY RECORDED_TIME DESC

SOLUTION

edtechdba

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kurt Reinhardt

This looks remarkably like code I've written for one of my Epic Clarity Hospital Clients (I tend to use the XYZ convention for subqueries/SQL Expressions). Can you please post your latest code with the recorded time as a field, even if you're getting an error? I think edtechdba is on the right track, but we need to see your code, too.

James0628

You should probably post your code, as rhinok suggested, but FWIW ...

The main WHERE (line 7) in edtechdba's code needs to include your original tests, on X.INPATIENT_DATA_ID and Y.FLO_MEAS_ID. Without those, you'll get results for any X.INPATIENT_DATA_ID and Y.FLO_MEAS_ID that happen to match the date returned by the sub-query.

IOW, the first WHERE should be

WHERE
X.INPATIENT_DATA_ID = "PAT_ENC"."INPATIENT_DATA_ID" AND
Y.FLO_MEAS_ID = '98765' AND
[RecordedTime] = (... sub-query ...)

Also, could a single patient have more than one entry on the same date (with FLO_MEAS_ID 98765)? If so, you'd need TOP 1 on the first Select, so that the SQL Expression only returns the value from one of those entries.

James

jul_to_cool

ASKER

Yes, the patient could have more than one entry on the same date. Below is the code (with errors)

(

SELECT MEAS_VALUE
FROM IP_DATA_STORE X
      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
WHERE Y.RECORDED_TIME = '987654'
      (
            SELECT MAX(Y1.RECORDED_TIME)
            FROM IP_DATA_STORE X1
                  INNER JOIN IP_FLWSHT_REC Z1
                        ON X1.INPATIENT_DATA_ID = Z1.INPATIENT_DATA_ID
                  INNER JOIN IP_FLWSHT_MEAS Y1
                        ON Z1.FSD_ID = Y1.FSD_ID
                  WHERE X1.INPATIENT_DATA_ID = "PAT_ENC"."INPATIENT_DATA_ID"
                        AND Y1.FLO_MEAS_ID = Y.FLO_MEAS_ID

      )
)

ASKER CERTIFIED SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

I'm glad that I could help, but most of the points should have gone to edtechdba. All I did was make some additions and corrections to your implementation of his code. You can use the "Request Attention" link to ask to have the question re-opened, and then split the points between us.

James