Solved

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

Posted on 2014-03-28
10
522 Views
Last Modified: 2014-04-08
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'
   
)
0
Comment
Question by:jul_to_cool
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 9

Expert Comment

by:edtechdba
ID: 39962049
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

0
 

Author Comment

by:jul_to_cool
ID: 39962368
I'm still getting the error since there is more than one value stored.  

The RECORDED_TIME field is in IP_FLWSHT_MEAS.
0
 
LVL 9

Expert Comment

by:edtechdba
ID: 39962419
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
0
 
LVL 9

Assisted Solution

by:edtechdba
edtechdba earned 250 total points
ID: 39962560
Example below using TOP 1 and RECORDED_TIME DESC.
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 TOP 1 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
			ORDER BY RECORDED_TIME DESC
	) 

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 39962968
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 39963678
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
0
 

Author Comment

by:jul_to_cool
ID: 39966686
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
                  
      )
)
0
 
LVL 34

Accepted Solution

by:
James0628 earned 250 total points
ID: 39966860
You have  Y.RECORDED_TIME = '987654', and then the sub-query.  If you look at edtechdba's code, that should be Y.RECORDED_TIME = (... sub-query ...).

 I've changed the Where, and added TOP 1 to the first Select, in case there is more than one entry for the most recent date.  If you could have more than one entry for the most recent date and they could have different values in MEAS_VALUE, and you need a specific one of those values (not just the first one), then you'd have to change the code to select the desired row.

 James

(
SELECT TOP 1 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' AND
    Y.RECORDED_TIME =
      (
            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
      )
)

Open in new window

0
 
LVL 34

Expert Comment

by:James0628
ID: 39977686
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
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now