Return the last row only - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return the last row only

Author

Topic

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-07-05 : 19:41:53

Hello!

I'm new to SQL and i've searched everywhere for the answer to this question but I can't seem to find the anwer anywhere. My question is.....

If there are multiple rows for a specific field, is there a way in SQL to only return the last row based on that specific field.

For example, I wrote a query that returns a list of patients who have a specific lab type. If a patient has had several labs taken, they show up in the result set several times (for each lab date). I only want to return the most recent lab date.

Here is my query........

SELECT id, resulted_test_desc, result_value, units, update_date

FROM dbo.lab_result LEFT OUTER JOIN
dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key

WHERE resulted_test_desc = 'Hemoglobin'

Any help would be greatly appreciated.

Thanks!

Amedeo

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-05 : 19:57:25

It's not clear which columns are in which tables, since you haven't prefixed the select list with this information, so I'm taking a shot in the dark here:


SELECT t.id, resulted_test_desc, result_value, units, t.update_date
FROM dbo.lab_result 
LEFT OUTER JOIN dbo.mpi 
ON dbo.lab_result.blind_key = dbo.mpi.chart 
LEFT OUTER JOIN dbo.mpi_xref 
ON dbo.mpi.chart = dbo.mpi_xref.blind_key
INNER JOIN
(
	SELECT id, MAX(update_date) AS update_date
	FROM dbo.lab_result 
	WHERE resulted_test_desc = 'Hemoglobin'
	GROUP BY id
) t
ON mpi.id = t.id AND mpi.update_date = t.update_date

Tara Kizer
http://weblogs.sqlteam.com/tarad/

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-07-06 : 12:44:43

Thank you very much for your attempt at helping me. Going forward I'll be sure to list which columns are in which tables.

I was looking at your suggestion and used the MAX() function and it appeared to work. However it only works if I do not include the lab result. This query will work(only if I leave out the lab result field):

SELECT mpi.hcl, mpi_xref.id, dbo.mpi.lname,
lab_result.resulted_test_desc,
lab_result.units, MAX(lab_result.update_date)
AS update_date
FROM lab_result LEFT OUTER JOIN
mpi ON lab_result.blind_key = mpi.chart
LEFT OUTER JOIN
mpi_xref ON mpi.chart = mpi_xref.blind_key

WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')

GROUP BY hcl,id, mpi.lname, lab_result.resulted_test_desc, lab_result.units

If I include the (lab_result.result_value) field in both the SELECT and GROUP BY statements the result set will have mulitple rows for each patient. One row for each lab result and date. I'd only like the most recent lab result date and value for each patient.

Thanks for your help. I really appreciate it.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 12:58:00

You need to use a derived table like in my example.

Please provide a data example of your issue. Show us what my query outputs, what your query outputs, and also what it actually should output. Make sure to illustrate what your data looks like before the query runs.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-07-06 : 15:50:04

I used your idea on the derived table and it worked. I'm not sure why I couldn't get it to work the first time. The only thing I found odd is that I had to specify again in another WHERE statement that I only wanted hemoglobin lab tests. If I remove this from the second WHERE statement......... AND resulted_test_desc = 'Hemoglobin', I recieve tests that aren't Hemoglobin but do have the same update_date as the Hemoglobin lab.

SELECT mpi.hcl, t.id, dbo.mpi.lname, UPPER(lab_result.resulted_test_desc) AS LabType,
lab_result.result_value AS LabResultValue, units, t.update_date

FROM dbo.lab_result LEFT OUTER JOIN dbo.mpi
ON dbo.lab_result.blind_key = dbo.mpi.chart
LEFT OUTER JOIN dbo.mpi_xref
ON dbo.mpi.chart = dbo.mpi_xref.blind_key
INNER JOIN(SELECT mpi_xref.id, MAX
(lab_result.update_date)
AS update_date
FROM dbo.mpi_xref INNER JOIN
dbo.lab_result ON
dbo.mpi_xref.blind_key =
dbo.lab_result.blind_key
WHERE resulted_test_desc
= 'Hemoglobin'
GROUP BY id) t ON
mpi_xref.id = t.id AND
lab_result.update_date = t.update_date

WHERE id_type = 'MR'
AND isnumeric(result_value) = 1
AND (resulted_test_desc = 'Hemoglobin')

ORDER BY hcl, t.id

Thank you so much for all of your help. I can't tell you how much I appreciate it. Thank you!

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-07-06 : 19:17:41

So I used the derived table concept for another query I just can't seem to get right. For this query I want to return only the most recent hemoglobin lab, epo lab, and ckd lab. The query below does indeed return the most recent date for each lab; however there are mutliple rows for the same patient. I pasted a sample of the result set.



SELECT     dbo.mpi.hcl, epo.id, UPPER(dbo.lab_result.resulted_test_desc) AS LabType, 
                      dbo.lab_result.result_value AS LabResultValue, dbo.lab_result.units, CONVERT(nvarchar(20),labs.update_date, 101) AS LabDate,
                      dbo.hm_master.health_maint_label, CONVERT(nvarchar(20),epo.EpoDate, 101) AS EpoDate, AKDHC_CKD.TestResultValue AS CkdTEST, 
                      CONVERT(nvarchar(20),ckd.TestDate, 101) AS CkdTestDate 

FROM         dbo.hm_master INNER JOIN
                      dbo.patient_hm ON dbo.hm_master.hm_master_key = dbo.patient_hm.hm_master_key INNER JOIN
                      dbo.lab_result LEFT OUTER JOIN
                      dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(lab_result_1.update_date) AS update_date
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   dbo.lab_result AS lab_result_1 ON mpi_xref_1.blind_key = lab_result_1.blind_key
                            WHERE      (lab_result_1.resulted_test_desc = 'Hemoglobin')
                            GROUP BY mpi_xref_1.id) AS labs ON dbo.mpi_xref.id = labs.id AND dbo.lab_result.update_date = labs.update_date ON 
                      dbo.patient_hm.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      AKDHC_CKD ON dbo.patient_hm.blind_key = AKDHC_CKD.Blind_Key INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(AKDHC_CKD_1.TestDate) AS TestDate
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   AKDHC_CKD AS AKDHC_CKD_1 ON mpi_xref_1.blind_key = AKDHC_CKD_1.blind_key
                            GROUP BY mpi_xref_1.id) AS ckd ON dbo.mpi_xref.id = ckd.id AND AKDHC_CKD.TestDate = ckd.TestDate INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(patient_hm_1.label_date) AS EpoDate
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   patient_hm AS patient_hm_1 ON mpi_xref_1.blind_key = patient_hm_1.blind_key
                            GROUP BY mpi_xref_1.id) AS epo ON dbo.mpi_xref.id = epo.id AND patient_hm.label_date = patient_hm.label_date 

WHERE id_type = 'MR' 
 AND health_maint_label = 'EPO'
 AND isnumeric(result_value) = 1 
 AND (resulted_test_desc = 'Hemoglobin')

ORDER BY hcl, labs.id

Office|id|LabType|LabResult|Units|LabDate|Epo|EpoDate|CkdResult|CkdDate
5202 100192 HEMOGLOBIN 12.9 g/dL 08/22/2006 EPO 06/20/2007 2.600000 08/11/2006
5202 100192 HEMOGLOBIN 12.9 g/dL 08/22/2006 EPO 06/20/2007 2.600000 08/11/2006
5202 100192 HEMOGLOBIN 12.9 g/dL 08/22/2006 EPO 06/20/2007 2.600000 08/11/2006
5202 100192 HEMOGLOBIN 12.9 g/dL 08/22/2006 EPO 06/20/2007 2.600000 08/11/2006
5202 100192 HEMOGLOBIN 12.9 g/dL 08/22/2006 EPO 06/20/2007 2.600000 08/11/2006
5202 101096 HEMOGLOBIN 9.8 g/dL 06/04/2007 EPO 06/18/2007 2.600000 12/05/2006
5202 101173 HEMOGLOBIN 11.4 g/dL 06/29/2007 EPO 07/03/2007 1.500000 12/21/2006
5202 101173 HEMOGLOBIN 11.4 g/dL 06/29/2007 EPO 07/03/2007 1.500000 12/21/2006
5202 101173 HEMOGLOBIN 11.4 g/dL 06/29/2007 EPO 07/03/2007 1.500000 12/21/2006
5202 101173 HEMOGLOBIN 11.4 g/dL 06/29/2007 EPO 07/03/2007 1.500000 12/21/2006

The number of rows returned for each patient matches the number of rows in the database for each epo date. It appears as though it is still returning a row for each epo date although it is displaying the most recent date.

Any help would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-07-06 : 21:32:16

You've shown us what your query is returning, however we also need to see what the data looks like in your tables. Please show us a sample of rows in each table. The sample should illustrate your problem.

The format of your sample data should be posted in INSERT INTO statements so that we can easily duplicate your issue in our environment. We also need the CREATE TABLE statements for all tables involved.

Here is an example of a post I started a while ago:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=42516

Tara Kizer
http://weblogs.sqlteam.com/tarad/

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-07-10 : 19:52:44

Tara - Let me start by saying this.....my original query(report), which was to return all patients who have a specific Hemoglobin lab result range within a specific date range BUT only look at the last lab date for each patient was completed thanks to your advice/solution.

With that being said, I thought I could tweak that query for anoher report I've been asked to produce. I'm kind of stuck right now as that the person who requested the report keeps changing the requirements on me. I simply wanted to fill you in on why I haven't posted a response to you seeing that you have been kind enough to assist me with this. I'll be sure to post the CREATE TABLE and INSERT INTO statements when I have the requirements only after I have tried to solve the issue myself.

Thanks for your help.

Amedeo

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-08 : 18:56:41

So I've been working on this for a few days and can't seem to figure out what I'm doing wrong. Based on the results I've been getting and comparing those results to the data stored in the database I realized that "tweaking" the query used for the first report in this thread simply wasn't going to work. With that being said, I've decided to start over.

What I would like to return from the database is a single row for each patient that has that pateint's last Hemoglobin lab date and result, the last ckd lab date and result, and the last date each patient has had an epo shot.

The following query returns all of the fields I'm looking for however there are rows for each hemoglobin lab, ckd lab and epo shot.

SELECT     dbo.mpi.hcl AS UnitId, dbo.lab_result.blind_key, dbo.mpi_xref.id AS ICChartID, dbo.mpi_xref.id_type, dbo.mpi.lname,  
                      UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value AS LabResultValue,  
                      dbo.lab_result.update_date AS LabResultDate, dbo.hm_master.health_maint_label, dbo.patient_hm.label_date, 
                      AKDHC_CKD.GFR_Stage, AKDHC_CKD.TestDate
                      

FROM         dbo.patient_hm INNER JOIN
                      dbo.hm_master ON dbo.patient_hm.hm_master_key = dbo.hm_master.hm_master_key INNER JOIN
                      dbo.mpi ON dbo.patient_hm.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      AKDHC_CKD ON dbo.patient_hm.blind_key = AKDHC_CKD.Blind_Key RIGHT OUTER JOIN
                      dbo.lab_result ON dbo.mpi.chart = dbo.lab_result.blind_key LEFT OUTER JOIN
                      dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key

WHERE id_type = 'MR' 
            AND isnumeric(result_value) = 1 
            AND (resulted_test_desc = 'Hemoglobin')
            AND health_maint_label = 'EPO'

For example, if a patient has had two hemoglobin labs, two ckd labs and two epo shots then there are eight rows for that patient. There are many patients in the database with over five hemoglobin labs, five ckd labs and five epo shots. This somewhat illustrates the problem of how many rows the query will return.

This data is entered into the database via a gui interface. A patient can recieve several types of shots. The shot given is selected in a drop down box which is stored in the hm_master.health_maint_label field and the date this shot is entered in the system is stored in the patient_hm.label_date field. Hence the WHERE statement includes the

AND health_maint_label = 'EPO'

to return EPO shots only.

I'd like to figure out how to return a single row for each patient that has the last date for each but can not seem to get it to work correctly. Any thoughts and/or suggestions would be greatly appreciated?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-09 : 09:13:02

Your post is confusing which suggests you are confused!
As has previously been suggested, you should really take the trouble to work out some test data along with the expected results which you could post with CREATE TABLE statements. Doing this will encourage people to answer your question and may even clarify your thoughts enough so you can work it out yourself.

Some problems with this post are:

1. In your query, the outer part of the joins to dbo.lab_result and dbo.mpi_xref seem to be negated by filters in the WHERE clause.
2. The Hemoglobin information can presumably be obtained by looking at dbo.lab_result.resulted_test_desc = 'Hemoglobin'. Does dbo.lab_result.resulted_test_desc ever equal 'ckd'? If not where does 'last ckd lab date and result' come from? Is it the information in AKDHC_CKD?
3. Is dbo.patient_hm a link table between dbo.mpi (MasterPatientInformation?) and dbo.hm_master? ie Is dbo.mpi to dbo.patient_hm a one to many link?
...

At a guess you may want something like the following. If you need more help ensure that DDL (CREATE TABLE statements etc), test data (not too much) and expected results are posted.

SELECT P.hcl AS UnitId
    ,X.id AS ICChartID
    ,X.id_type
    ,P.lname
    ,V1.LastHemoglobinLabDate
    ,V1.LastHemoglobinValue
    ,V2.TestDate
    ,V2.GFR_Stage
    ,V3.LastEPODate
FROM dbo.mpi P
    JOIN dbo.mpi_xref X
        ON P.chart = X.blind_key
    LEFT JOIN (
            SELECT R.blind_key
                ,R.update_date AS LastHemoglobinLabDate
                ,R.result_value AS LastHemoglobinValue
            FROM dbo.lab_result R
                JOIN (
                        SELECT R1.blind_key, MAX(R1.update_date) AS MaxDate
                        FROM dbo.lab_result R1
                        -- ISNUMERIC may not be what you think.
                        WHERE ISNUMERIC(R1.result_value) = 1
                            AND R1.resulted_test_desc = 'Hemoglobin'
                        GROUP BY R1.blind_key
                    ) D1
                    ON R.blind_key = D1.blind_key
                        AND R.update_date = D1.MaxDate
        ) V1
        ON P.chart = V1.blind_key
    LEFT JOIN (
            SELECT A.Blind_Key
                ,A.TestDate
                ,A.GFR_Stage
            FROM AKDHC_CKD A
                JOIN (
                        SELECT A1.Blind_Key, MAX(A1.TestDate) AS MaxTestDate
                        FROM AKDHC_CKD A1
                        GROUP BY A1.Blind_Key
                    ) AD1
                    ON A.Blind_Key = AD1.Blind_Key
                        AND A.TestDate = AD1.MaxTestDate
        ) V2
        ON P.chart = V2.blind_key
    LEFT JOIN (
            SELECT XP.blind_key, MAX(XP.label_date) AS LastEPODate
            FROM dbo.hm_master M
                JOIN dbo.patient_hm XP
                    ON M.hm_master_key = XP.hm_master_key
            WHERE M.health_maint_label = 'EPO'
        ) V3
        ON P.chart = V3.blind_key
WHERE X.id_type = 'MR'

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-09 : 12:32:37

Hello Ifor! Thank you for your feedback.

1. In your query, the outer part of the joins to dbo.lab_result and dbo.mpi_xref seem to be negated by filters in the WHERE clause.
I'm not sure what you mean by this. I am not doubting that you are right, I'm just not sure what you are referring to?
2. The Hemoglobin information can presumably be obtained by looking at dbo.lab_result.resulted_test_desc = 'Hemoglobin'.
You are correct. There are other lab type values in this field.
Does dbo.lab_result.resulted_test_desc ever equal 'ckd'? If not where does 'last ckd lab date and result' come from?
No - ckd labs never come from dbo.lab_result.resulted_test_desc. 'last ckd lab date and result come from the AKDHC_CKD table
Is it the information in AKDHC_CKD?
Yes - the only lab type stored in the AKDHC_CKD table are ckd labs.

3. Is dbo.patient_hm a link table between dbo.mpi (MasterPatientInformation?) and dbo.hm_master? ie Is dbo.mpi to dbo.patient_hm a one to many link?
Yes - dbo.patient_hm is a link table between dbo.mpi(MasterPatientInformation) and dbo.hm_master. dbo.mpi to dbo.patient_hm is a one to many link.

I tried your query and recieved this error...
Column 'XP.blind_key' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause. I understand and can foolow along with tthe derived tables you are creating in the FROM statement. I'm a little confused about the aliased tbales in the SELECT statement.

In regards to posting sample data should i only post sample data for the tables and fields I am looking for results from? Some of these tables contain numerous fields that I'm not looking to retreive data from? Should I post sample data from tables that I don't need any fields retreived from (i.e dbo.patient_hm)?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-09 : 12:51:15

Please see my last post and check out the thread in which I asked for help. I posted enough sample data to illustrate my problem and only included the columns that were relevant to the problem. I also did not post my actual data or columns as that was proprietary information. I changed the names and data so that people on the Internet could understand what I wanted.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-08-09 : 13:08:28

1. Run the following to see what I mean.

-- *** Test Data ***
CREATE TABLE #temp1
(
    T1ID int NOT NULL PRIMARY KEY
    ,T1Name varchar(20) NOT NULL
)
INSERT INTO #temp1
SELECT 1, 'T1Name1' UNION ALL
SELECT 2, 'T1Name2' UNION ALL
SELECT 3, 'T1Name3'

CREATE TABLE #temp2
(
    T2ID int NOT NULL PRIMARY KEY
    ,T1ID int NOT NULL
    ,T2Name varchar(20) NOT NULL
)
INSERT INTO #temp2
SELECT 1, 1, 't1' UNION ALL
SELECT 2, 1, 't1' UNION ALL
SELECT 3, 3, 't1' UNION ALL
SELECT 4, 3, 't1'
-- *** End Test Data ***

-- Outer Join
SELECT *
FROM #temp1 T1
    LEFT JOIN #temp2 T2
        ON T1.T1ID = T2.T1ID

-- Outer Join with filer on joined table in WHERE clause
SELECT *
FROM #temp1 T1
    LEFT JOIN #temp2 T2
        ON T1.T1ID = T2.T1ID
WHERE T2.T2Name = 't1' -- The NULLs in T2 will never equal 't1'

-- Inner Join - same as above - ie the above is a waste of an outer join.
SELECT *
FROM #temp1 T1
    JOIN #temp2 T2
        ON T1.T1ID = T2.T1ID
WHERE T2.T2Name = 't1'

-- Outer Join with filer on joined table in ON clause. This may be what you wanted.
SELECT *
FROM #temp1 T1
    LEFT JOIN #temp2 T2
        ON T1.T1ID = T2.T1ID
            AND T2.T2Name = 't1'

2. I am still not sure which columns you need to refer to in AKDHC_CKD. Has it the same structure as dbo.lab_result?

<quote>I tried your query and recieved this error...</quote>
I forgot to add GROUP BY XP.blind_key to the derived table V3. With no sample data it could not be tested.

<quote>In regards to posting sample data should i only post sample data for the tables and fields I am looking for results from?</quote>
Yes, you just need enough data and columns to define the problem. You should indicate primary and foreign keys where appropriate.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-09 : 15:05:39

If you are using SQL Server 2005, you can easily do this with the new ROW_NUMBER() function.

E 12°55'05.25"
N 56°04'39.16"

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-12 : 22:23:30

quote:
Originally posted by Ifor

1. Run the following to see what I mean.

Thanks for the example. I used it when creating my CREATE TABLE statements.

2. I am still not sure which columns you need to refer to in AKDHC_CKD. Has it the same structure as dbo.lab_result?

I think my next post will answer this.

<quote>I tried your query and recieved this error...</quote>
I forgot to add GROUP BY XP.blind_key to the derived table V3. With no sample data it could not be tested.

I added the GROUP BY and it worked. Thank you!

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-12 : 22:29:54

quote:
Originally posted by Peso

If you are using SQL Server 2005, you can easily do this with the new ROW_NUMBER() function.

Based on the artices I found, it appears as though the ROW_NUMBER function isn't what I'm looking for. I'm lookin gto return the last row for each patient. There could be hundrends of rows returned for one patient and only two rows returned for another.

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-12 : 22:32:16

Here are my create table statements (I hope I did this right - I tried to test it and after sereral errors I made enough corrections to get a "CREATE Table permission denied" error so I am assuming this is correct).....


--mpi table is the master patient index. Used to retreive patient
--info such as name, dob, address
--hcl stands for health chart location (in other words
--what office does this patient go to?)
--primary key is chart (which is labeled as 
--blind_key in other tables
--In other words, blind_key is the same as chart

CREATE TABLE mpi 
(
   chart decimal(18,0) IDENTITY(1,1) 
   ,hcl varchar(10) NULL
)

INSERT INTO mpi (chart, hcl)
SELECT 4594, 5087 UNION ALL
SELECT 12368, 5087 UNION ALL
SELECT 23834, 5087 UNION ALL
SELECT 41245, 5087 UNION ALL
SELECT 45737, 5087 UNION ALL
SELECT 76163, 5087 UNION ALL
SELECT 586, 5201 UNION ALL
SELECT 2675, 5201 UNION ALL
SELECT 24634, 5201 UNION ALL
SELECT 35512, 5201 



--mpi_xref table contains patient ids such as id and ssn
--i link to this table to retrieve the EMR id
CREATE TABLE mpi_xref 
(
   blind_key decimal(18,0) NOT NULL --(FK) (IE1.1) 
   ,id varchar(20) NOT NULL --(IE2.1)
)

INSERT INTO mpi_xref (blind_key, id)
SELECT 4594, 105518 UNION ALL
SELECT 12368, 114577 UNION ALL
SELECT 23834, 127485 UNION ALL
SELECT 41245, 147001 UNION ALL
SELECT 45737, 152167 UNION ALL
SELECT 76163, 187516 UNION ALL
SELECT 586, 100735 UNION ALL
SELECT 2675, 103304 UNION ALL
SELECT 24634, 128372 UNION ALL
SELECT 35512, 140567 



--patient_hm table - i use this table to retrieve epo
--shot data. I retrieve the label date (the date a shot was
--given)from this table and then link to hm_master to 
--to retreive the type of shot given (health_maint_label = 'EPO')
--I link from the patient_hm table to the hm_master table
--on hm_master_key
CREATE TABLE patient_hm 
(
   blind_key decimal(18,0) NOT NULL --(FK) (AK1.3) 
   ,hm_master_key int NOT NULL --(FK) (AK1.1,IE1.1)
   ,label_date datetime NULL --(AK1.2)
)

INSERT INTO patient_hm (blind_key, hm_master_key, label_date)
SELECT 4594, 45411189, '2006-09-20 00:00:00:000' UNION ALL
SELECT 12368, 45411189, '2006-10-18 00:00:00:000' UNION ALL
SELECT 23834, 45411189, '2007-07-03 00:00:00:000' UNION ALL
SELECT 41245, 45411189, '2007-05-31 00:00:00:000' UNION ALL
SELECT 41245, 45411189, '2007-06-29 00:00:00:000' UNION ALL
SELECT 41245, 45411189, '2007-07-27 00:00:00:000' UNION ALL
SELECT 41245, 45411189, '2007-03-14 00:00:00:000' UNION ALL
SELECT 41245, 45411189, '2007-04-11 00:00:00:000' UNION ALL
SELECT 45737, 45411189, '2007-05-09 00:00:00:000' UNION ALL
SELECT 76163, 45411189, '2007-01-24 00:00:00:000' UNION ALL
SELECT 76163, 45411189, '2007-02-21 00:00:00:000' UNION ALL
SELECT 76163, 45411189, '2007-03-08 00:00:00:000' UNION ALL
SELECT 586, 45411189, '2007-06-27 00:00:00:000' UNION ALL
SELECT 586, 45411189, '2007-07-24 00:00:00:000' UNION ALL
SELECT 2675, 45411189, '2007-06-13 00:00:00:000' UNION ALL
SELECT 2675, 45411189, '2007-07-12 00:00:00:000' UNION ALL
SELECT 24634, 45411189, '2007-07-09 00:00:00:000' UNION ALL
SELECT 24634, 45411189, '2007-06-15 00:00:00:000' UNION ALL
SELECT 35512, 45411189, '2007-05-17 00:00:00:000' UNION ALL
SELECT 35512, 45411189, '2007-03-16 00:00:00:000' UNION ALL
SELECT 35512, 45411189, '2007-03-29 00:00:00:000' UNION ALL
SELECT 35512, 45411189, '2007-04-05 00:00:00:000' 



--hm_master table – I link to this table to only return patients with epo shots
--while working on these create table statements I realized I don’t even need to 
--link to this table. 
CREATE TABLE hm_master
(
    hm_master_key int NOT NULL --(PK)
   ,health_maint_label varchar(50) NOT NULL --(AK1.1)
)

SELECT 45411189, 'epo'




--lab_result table – this is where I retrieve a patients lab type
--lab value and date. I narrow it down to only hemoglobin labs
--in the WHERE statement.
 CREATE TABLE lab_result 
(
   blind_key decimal(18,0) NOT NULL --(FK) (IE2.1) 
   ,resulted_test_desc varchar(50) NULL
   ,update_date datetime NULL
)

INSERT INTO lab_result (blind_key, resulted_test_desc, update_date)
SELECT 4594, 'HEMOGLOBIN', '2006-08-22 00:00:00:000' UNION ALL
SELECT 12368, 'HEMOGLOBIN', '2006-11-08 00:00:00:000' UNION ALL
SELECT 23834, 'HEMOGLOBIN', '2006-07-21 00:00:00:000' UNION ALL
SELECT 41245, 'HEMOGLOBIN', '2006-05-22 00:00:00:000' UNION ALL
SELECT 45737, 'HEMOGLOBIN', '2007-05-18 00:00:00:000' UNION ALL
SELECT 45737, 'HEMOGLOBIN', '2007-07-27 00:00:00:000' UNION ALL
SELECT 76163, 'HEMOGLOBIN', '2007-03-03 00:00:00:000' UNION ALL
SELECT 586, 'HEMOGLOBIN', '2006-11-23 00:00:00:000' UNION ALL
SELECT 586, 'HEMOGLOBIN', '2007-08-07 00:00:00:000' UNION ALL
SELECT 586, 'HEMOGLOBIN', '2007-06-10 00:00:00:000' UNION ALL
SELECT 586, 'HEMOGLOBIN', '2007-07-10 00:00:00:000' UNION ALL
SELECT 2675, 'HEMOGLOBIN', '2006-11-29 00:00:00:000' UNION ALL
SELECT 2675, 'HEMOGLOBIN', '2007-08-08 00:00:00:000' UNION ALL
SELECT 2675, 'HEMOGLOBIN', '2006-11-09 00:00:00:000' UNION ALL
SELECT 2675, 'HEMOGLOBIN', '2007-07-11 00:00:00:000' UNION ALL
SELECT 24634, 'HEMOGLOBIN', '2007-06-05 00:00:00:000' UNION ALL
SELECT 24634, 'HEMOGLOBIN', '2007-05-22 00:00:00:000' UNION ALL
SELECT 24634, 'HEMOGLOBIN', '2006-03-17 00:00:00:000' UNION ALL
SELECT 24634, 'HEMOGLOBIN', '2006-12-28 00:00:00:000' UNION ALL
SELECT 35512, 'HEMOGLOBIN', '2007-06-19 00:00:00:000' 



--AKDHC_CKD table – this table is created from a stored procedure. The results of a 
--creatin lab, the patients age, race and gender are used calculate a CKD Test
--result(referred to as MDRD).
--This CKD test result along with the date of the creatin lab are retrieved from 
--this table.
 CREATE TABLE AKDHC_CKD 
(
   blind_key decimal(18,0) NOT NULL --(PK)
   ,MDRD varchar(10) NULL
   ,TestDate datetime NULL

)

INSERT INTO AKDHC_CKD (blind_key, MDRD,TestDate)
SELECT 4594, 4.4, '2006-08-22 00:00:00:000' UNION ALL
SELECT 12368, 1.2, '2006-12-09 00:00:00:000' UNION ALL
SELECT 23834, 2.3, '2007-06-18 00:00:00:000' UNION ALL
SELECT 23834, 2.3, '2007-07-21 00:00:00:000' UNION ALL
SELECT 23834, 2.2, '2007-07-02 00:00:00:000' UNION ALL
SELECT 41245, 1.2, '2006-11-16 00:00:00:000' UNION ALL
SELECT 45737, 1.1, '2007-04-25 00:00:00:000' UNION ALL
SELECT 45737, 1.0, '2007-05-18 00:00:00:000' UNION ALL
SELECT 76163, 11.1, '2007-03-03 00:00:00:000' UNION ALL
SELECT 76163, 8.1, '2007-01-06 00:00:00:000' UNION ALL
SELECT 76163, 8.8, '2007-01-30 00:00:00:000' UNION ALL
SELECT 586, 1.2, '2007-06-09 00:00:00:000' UNION ALL
SELECT 586, 1.6, '2006-10-21 00:00:00:000' UNION ALL
SELECT 586, 1.4, '2007-03-03 00:00:00:000' UNION ALL
SELECT 2675, 2.8, '2007-01-18 00:00:00:000' UNION ALL
SELECT 2675, 2.6, '2007-02-04 00:00:00:000' UNION ALL
SELECT 2675, 2.3, '2006-11-09 00:00:00:000' UNION ALL
SELECT 24634, 1.6, '2006-11-23 00:00:00:000' UNION ALL
SELECT 24634, 1.8, '2007-03-17 00:00:00:000' UNION ALL
SELECT 24634, 1.5, '2007-05-31 00:00:00:000' UNION ALL
SELECT 24634, 2.7, '2007-04-25 00:00:00:000' UNION ALL
SELECT 35512, 3.1, '2007-04-09 00:00:00:000' UNION ALL
SELECT 35512, 2.1, '2007-07-19 00:00:00:000'

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-14 : 19:49:42

quote:

Originally posted by aferoce@akdhc.com

So I used the derived table concept for another query I just can't seem to get right. For this query I want to return only the most recent hemoglobin lab, epo lab, and ckd lab. The query below does indeed return the most recent date for each lab; however there are mutliple rows for the same patient. I pasted a sample of the result set.



SELECT     dbo.mpi.hcl, epo.id, UPPER(dbo.lab_result.resulted_test_desc) AS LabType, 
                      dbo.lab_result.result_value AS LabResultValue, dbo.lab_result.units, CONVERT(nvarchar(20),labs.update_date, 101) AS LabDate,
                      dbo.hm_master.health_maint_label, CONVERT(nvarchar(20),epo.EpoDate, 101) AS EpoDate, AKDHC_CKD.TestResultValue AS CkdTEST, 
                      CONVERT(nvarchar(20),ckd.TestDate, 101) AS CkdTestDate 

FROM         dbo.hm_master INNER JOIN
                      dbo.patient_hm ON dbo.hm_master.hm_master_key = dbo.patient_hm.hm_master_key INNER JOIN
                      dbo.lab_result LEFT OUTER JOIN
                      dbo.mpi ON dbo.lab_result.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(lab_result_1.update_date) AS update_date
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   dbo.lab_result AS lab_result_1 ON mpi_xref_1.blind_key = lab_result_1.blind_key
                            WHERE      (lab_result_1.resulted_test_desc = 'Hemoglobin')
                            GROUP BY mpi_xref_1.id) AS labs ON dbo.mpi_xref.id = labs.id AND dbo.lab_result.update_date = labs.update_date ON 
                      dbo.patient_hm.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      AKDHC_CKD ON dbo.patient_hm.blind_key = AKDHC_CKD.Blind_Key INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(AKDHC_CKD_1.TestDate) AS TestDate
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   AKDHC_CKD AS AKDHC_CKD_1 ON mpi_xref_1.blind_key = AKDHC_CKD_1.blind_key
                            GROUP BY mpi_xref_1.id) AS ckd ON dbo.mpi_xref.id = ckd.id AND AKDHC_CKD.TestDate = ckd.TestDate INNER JOIN
                          (SELECT     mpi_xref_1.id, MAX(patient_hm_1.label_date) AS EpoDate
                            FROM          dbo.mpi_xref AS mpi_xref_1 INNER JOIN
                                                   patient_hm AS patient_hm_1 ON mpi_xref_1.blind_key = patient_hm_1.blind_key
                            GROUP BY mpi_xref_1.id) AS epo ON dbo.mpi_xref.id = epo.id AND patient_hm.label_date = patient_hm.label_date 

WHERE id_type = 'MR' 
 AND health_maint_label = 'EPO'
 AND isnumeric(result_value) = 1 
 AND (resulted_test_desc = 'Hemoglobin')

ORDER BY hcl, labs.id

So I figured out why the query in this post was still returning multiple rows even though each row correctly had the last date for each lab. The last statement of the FROM statement currently is:

GROUP BY mpi_xref_1.id) AS epo ON dbo.mpi_xref.id = epo.id AND patient_hm.label_date = patient_hm.label_date

When it should be

GROUP BY mpi_xref_1.id) AS epo ON dbo.mpi_xref.id = epo.id AND patient_hm.label_date = epo.EpoDate

Now that the query only returns the last row for each patient I've noticed that this query is not including all of the patients it should. Is is safe to assume that if a query doesn not return all of the rows you are looking for that it is an issue in the JOINS?

aferoce@akdhc.com
Starting Member

12 Posts

Posted - 2007-08-23 : 11:53:47

This is the query I used that resolved my issue. Thanks to all who tried to help.

--Selects the rows with the max date from the SELECT statement below
(SELECT id, lname, MAX(obs_date_time) AS LabDate, Max(label_date) AS EpoDate, Max(testdate) AS CkdTestDate FROM 
--RawDataQuery (returns all rows)
(SELECT     dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, 
                      UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units, 
                      dbo.lab_result.obs_date_time, dbo.hm_master.health_maint_label, dbo.patient_hm.label_date,  
                      AKDHC_CKD.CKDPatientYN, AKDHC_CKD.TestCode, AKDHC_CKD.TestResultValue, AKDHC_CKD.MDRD, AKDHC_CKD.GFR_Stage, 
                      AKDHC_CKD.TestDate
FROM         dbo.patient_hm INNER JOIN
                      dbo.hm_master ON dbo.patient_hm.hm_master_key = dbo.hm_master.hm_master_key RIGHT OUTER JOIN
                      dbo.mpi ON dbo.patient_hm.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      AKDHC_CKD ON dbo.mpi.chart = AKDHC_CKD.Blind_Key LEFT OUTER JOIN
                      dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key LEFT OUTER JOIN
                      dbo.lab_result ON dbo.mpi.chart = dbo.lab_result.blind_key

WHERE id_type = 'MR' 
            AND isnumeric(result_value) = 1 
            AND (resulted_test_desc = 'Hemoglobin')
            AND health_maint_label = 'EPO') ResultSet1
GROUP BY lname, id)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:07:13

You doubt my knowledge?

quote:
Originally posted by aferoce@akdhc.com

Based on the artices I found, it appears as though the ROW_NUMBER function isn't what I'm looking for. I'm lookin gto return the last row for each patient. There could be hundrends of rows returned for one patient and only two rows returned for another.

See the example code below. You NEED the ROW_NUMBER() function

-- Prepare sample data
declare @patients as table (patid int, patname sysname)

insert	@patients
select	1, 'aferoce' union all
select	2, 'peso' union all
select	3, 'twit'

declare	@labs table (labid int, patid int, thedate datetime, answer sysname)

insert	@labs
select	10, 2, '20070415', 'Beerbelly' union all
select	1, 1, '20070101', 'Hemoglobin OK' union all
select	4, 1, '20070801', 'Brain surgery needed' union all
select	15, 2, '20070304', 'Hairloss' union all
select	7, 2, '20070215', 'Overweight' union all
select	21, 2, '20070820', 'Perfect condition' union all
select	11, 2, '20070520', 'Crazy' union all
select	5, 1, '20070601', 'Kidneys gone' union all
select	99, 2, '20070603', 'Stabil'

-- Stage the data
;WITH cte_stage (PatID, PatName, LabID, theDate, Answer, RecID)
AS (
	SELECT		p.PatID,
			p.PatName,
			l.LabID,
			l.theDate,
			l.Answer,
			ROW_NUMBER() OVER (PARTITION BY p.PatID ORDER BY l.theDate DESC) AS RecID
	FROM		@Patients AS p
	LEFT JOIN	@Labs AS l ON l.PatID = p.PatID
)

-- Show the expected output
SELECT		PatID,
		PatName,
		LabID,
		theDate,
		Answer
FROM		cte_stage
WHERE		RecID = 1
ORDER BY	PatID

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-23 : 12:10:55

quote:

Originally posted by aferoce@akdhc.com

This is the query I used that resolved my issue. Thanks to all who tried to help.

--Selects the rows with the max date from the SELECT statement below
(SELECT id, lname, MAX(obs_date_time) AS LabDate, Max(label_date) AS EpoDate, Max(testdate) AS CkdTestDate FROM 
--RawDataQuery (returns all rows)
(SELECT     dbo.mpi.hcl, dbo.mpi_xref.id, dbo.mpi.lname, dbo.mpi.fname, dbo.mpi.mname, 
                      UPPER(dbo.lab_result.resulted_test_desc) AS LabType, dbo.lab_result.result_value, dbo.lab_result.units, 
                      dbo.lab_result.obs_date_time, dbo.hm_master.health_maint_label, dbo.patient_hm.label_date,  
                      AKDHC_CKD.CKDPatientYN, AKDHC_CKD.TestCode, AKDHC_CKD.TestResultValue, AKDHC_CKD.MDRD, AKDHC_CKD.GFR_Stage, 
                      AKDHC_CKD.TestDate
FROM         dbo.patient_hm INNER JOIN
                      dbo.hm_master ON dbo.patient_hm.hm_master_key = dbo.hm_master.hm_master_key RIGHT OUTER JOIN
                      dbo.mpi ON dbo.patient_hm.blind_key = dbo.mpi.chart LEFT OUTER JOIN
                      AKDHC_CKD ON dbo.mpi.chart = AKDHC_CKD.Blind_Key LEFT OUTER JOIN
                      dbo.mpi_xref ON dbo.mpi.chart = dbo.mpi_xref.blind_key LEFT OUTER JOIN
                      dbo.lab_result ON dbo.mpi.chart = dbo.lab_result.blind_key

WHERE id_type = 'MR' 
            AND isnumeric(result_value) = 1 
            AND (resulted_test_desc = 'Hemoglobin')
            AND health_maint_label = 'EPO') ResultSet1
GROUP BY lname, id)

This query is flawed!
There is absolutely no gurantee that the three MAX thingies come from the same record!!!

Please reconsider! See my solution above.
All you have to do is the replace the staging part in the cte with your JOINS above.

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources