Tech or Treat! Write an article about your scariest tech disaster to win gadgets!Learn more

x
?
Solved

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

Posted on 2014-03-28
10
Medium Priority
?
559 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 9

Assisted Solution

by:edtechdba
edtechdba earned 1000 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
 
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 35

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 35

Accepted Solution

by:
James0628 earned 1000 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 35

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

647 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