Most Recent Record - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Most Recent Record

Author

Topic

ybechar
Starting Member

8 Posts

Posted - 2008-07-08 : 16:46:09

Hi,

I have a RESULT table that has the following fields:
- DETAIL_ID -> UNIQUE IDENTIFIER
- HEADER_ID
- COLLECTION_DATE
- CPI
- RSLT_TEST_CODE
- VALUE
- DIVISION_ID

I need to get the most recent value (based on the collection_date) for every result code (RSLT_TEST_CODE).
and then join the result with a couple of tables: PATIENT & PAT_DIAG_CODE based on the CPI and DIVISION_ID (patient unique identifier).

Thank you,

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-07-08 : 17:33:19

One way is to use APPLY (cross or outer)


select ot.<columns>
       ,ca.DETAIL_ID 
       ,ca.HEADER_ID
       ,ca.COLLECTION_DATE
       ,ca.CPI
       ,ca.RSLT_TEST_CODE
       ,ca.VALUE
       ,ca.DIVISION_ID
from   OtherTable ot
cross apply (
       select top 1
              DETAIL_ID 
              ,HEADER_ID
              ,COLLECTION_DATE
              ,CPI
              ,RSLT_TEST_CODE
              ,VALUE
              ,DIVISION_ID
       from   [Result]
       where  RSLT_TEST_CODE = ot.RSLT_TEST_CODE
       order by collection_date desc
       ) ca

Be One with the Optimizer
TG

ybechar
Starting Member

8 Posts

Posted - 2008-07-08 : 19:28:16

That didn't work for some reason I am getting that CROSS APPLY is not supported.
I was thinking that it would be better to use something like and INNER JOIN with MAX(COLLECTION_DATE). The problem that I have with this statement is that I don't know how to get the VALUE for the most recent COLLECTION_DATE.
Thanks for your help,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 19:30:16

"I am getting that CROSS APPLY is not supported"
Are you using SQL Server 2005 ?

KH
[spoiler]Time is always against us[/spoiler]

ybechar
Starting Member

8 Posts

Posted - 2008-07-08 : 19:43:07

Yes, I am.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 19:59:50

Check that your database compatibility level is set to 90

KH
[spoiler]Time is always against us[/spoiler]

ybechar
Starting Member

8 Posts

Posted - 2008-07-08 : 20:51:57

HEY,

I got it to work I used a different approach here is the result for those who are still struggling with this issue:

SELECT R.RESULT_TEST_CODE, R.CPI, R.VALUE, R.COLLECTION_DATE, VP.LASTNAME, VP.FIRSTNAME, VP.DOB, PDX.DIAG_CODE, VP.DIVISION_ID, VP.DIV_NAME
FROM (SELECT MAX(COLLECTION_DATE) AS COLLECTION_DATE, CPI, RESULT_TEST_CODE, DIVISION_ID
FROM VW_HEADER_DETAIL_LAB
GROUP BY CPI, RESULT_TEST_CODE, DIVISION_ID) AS TEMP INNER JOIN
VW_HEADER_DETAIL_LAB AS R ON TEMP.CPI = R.CPI AND TEMP.COLLECTION_DATE = R.COLLECTION_DATE AND
TEMP.RESULT_TEST_CODE = R.RESULT_TEST_CODE AND TEMP.DIVISION_ID = R.DIVISION_ID LEFT OUTER JOIN
PAT_DIAG_DIV AS PDX ON R.CPI = PDX.CPI AND R.DIVISION_ID = PDX.DIVISION_ID LEFT OUTER JOIN
VW_PATIENT AS VP ON R.CPI = VP.CPI AND R.DIVISION_ID = VP.DIVISION_ID

By the way this forum rocks!!!!
Thanks,
ybechar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources