Max Date Issue in Multi-Join Query 1

[jimdandy1]

I'm having a horrible time trying to figure this out so I'm hoping that someone here can help me out.

I have a query that returns abnormal test results for patients that are in-house. The issue that I'm having is that I need to return only the must recent test result. There are only going to be 2-5 patients that are returned and each patient can have multiple tests done during their stay and those tests can be repeated during their stay. Example. Test1 on Day1, Day2, Day3 and Test2 on Day1, Day2, Day3, and Test3 on Day1, Day3, and etc. So here is the layout of my data that I am getting back, the data I need to see, and my query. Thank you in advance!!

Here is what I currently get.

[u]PatLocation[/u]    [u]OrderName[/u]    [u]PatName[/u]    [u]PatAcctID[/u]    [u]TestDescr[/u]    [u]TestResult[/u]    [u]CreationTime[/u] 
HOSP1          DEPAKOTE     Patient1     123456     Creatinine     .67         2013-04-01 13:00:00
HOSP1          DEPAKOTE     Patient1     123456     Creatinine     .50         2013-04-02 10:00:00
HOSP1          DEPAKOTE     Patient1     123456     Creatinine     .78         2013-04-03 15:30:00
HOSP1          DEPAKOTE     Patient1     123456     Creatinine     .80         2013-04-05 17:06:00
HOSP1          DEPAKOTE     Patient1     123456     Monocyte        10         2013-04-01 10:00:00
HOSP1          DEPAKOTE     Patient1     123456     Monocyte        12         2013-04-02 14:21:00
HOSP1          DEPAKOTE     Patient1     123456     Monocyte        14         2013-04-03 12:16:00

Here is what I need to get.

[u]PatLocation[/u]    [u]OrderName[/u]    [u]PatName[/u]    [u]PatAcctID[/u]    [u]TestDescr[/u]    [u]TestResult[/u]    [u]CreationTime[/u] 
HOSP1          DEPAKOTE     Patient1     123456     Creatinine     .80         2013-04-05 17:06:00
HOSP1          DEPAKOTE     Patient1     123456     Monocyte        14         2013-04-03 12:16:00

Here is my current SQL. The field CreationTime is the one I'm having a tough time with getting only the recent values. I've didn't include the code that had my failed attempts at trying to get the max creationtime.

SELECT DISTINCT 
	ho.OrdName
	,hp.LastName + ', ' + hp.FirstName + ' ' + ISNULL(hp.MiddleName, '') AS FullName
	,hpv.PatLocName
	,hpv.PatAcctID
	,hirs.EnteredByName
	,hs.[Description]
	,hir.AbFlag
	,hir.ResVal
	,hir.CreationTime
	,hir.RefRange
	,hir.UnitOfReferenceRange
FROM Orders ho WITH (NOLOCK)
	INNER JOIN InvResult hir WITH (NOLOCK) ON hir.Pat_oid = ho.Pat_oid AND hir.PatVisit_oid = ho.PatVisit_oid
	INNER JOIN PatVisit hpv WITH (NOLOCK) ON hir.Pat_oid = hpv.Pat_oid AND hir.PatVisit_oid = hpv.ObjectID AND hpv.IsDeleted = 0 AND hpv.VisitEndDateTime IS NULL
	INNER JOIN Pat hp WITH (NOLOCK) ON hpv.Pat_oid = hp.ObjectID AND hp.IsDeleted = 0
	INNER JOIN InvResultSuppInfo hirs WITH (NOLOCK) ON hir.ResSuppInfo_oid = hirs.ObjectID 
	LEFT OUTER JOIN Service hs WITH (NOLOCK) ON hir.Service_oid = hs.ObjectID AND hs.IsDeleted = 0
WHERE ho.OrdAbbr IN ('7000280', '7000585')     
	AND ho.OrdStatModCode = 27
	AND hirs.EnteredByName = 'LAB'
	AND (hir.AbFlag IS NOT NULL AND hir.AbFlag <> '')
	AND hpv.patloc_oid in (199, 225, 207, 215)

Thank you in advance!

John

 

[yelworcm]

The NOLOCK hints are probably not needed, and are usually a bad idea. That aside, this is actually fairly simple, once you learn the trick. The trick is a Common Table Extension, combined with a Windowing function:

with CTE_Tests
as (
SELECT DISTINCT 
	ho.OrdName
	,hp.LastName + ', ' + hp.FirstName + ' ' + ISNULL(hp.MiddleName, '') AS FullName
	,hpv.PatLocName
	,hpv.PatAcctID
	,hirs.EnteredByName
	,hs.[Description]
	,hir.AbFlag
	,hir.ResVal
	,hir.CreationTime
	,hir.RefRange
	,hir.UnitOfReferenceRange
        ,row_number() over (partition by hpv.PatAcctID, hs.[Description], order by hir.CreationTime desc) as rownum
FROM Orders ho WITH (NOLOCK)
	INNER JOIN InvResult hir WITH (NOLOCK) ON hir.Pat_oid = ho.Pat_oid AND hir.PatVisit_oid = ho.PatVisit_oid
	INNER JOIN PatVisit hpv WITH (NOLOCK) ON hir.Pat_oid = hpv.Pat_oid AND hir.PatVisit_oid = hpv.ObjectID AND hpv.IsDeleted = 0 AND hpv.VisitEndDateTime IS NULL
	INNER JOIN Pat hp WITH (NOLOCK) ON hpv.Pat_oid = hp.ObjectID AND hp.IsDeleted = 0
	INNER JOIN InvResultSuppInfo hirs WITH (NOLOCK) ON hir.ResSuppInfo_oid = hirs.ObjectID 
	LEFT OUTER JOIN Service hs WITH (NOLOCK) ON hir.Service_oid = hs.ObjectID AND hs.IsDeleted = 0
WHERE ho.OrdAbbr IN ('7000280', '7000585')     
	AND ho.OrdStatModCode = 27
	AND hirs.EnteredByName = 'LAB'
	AND (hir.AbFlag IS NOT NULL AND hir.AbFlag <> '')
	AND hpv.patloc_oid in (199, 225, 207, 215) )

select *
from CTE_tests
where rownum = 1

This is completely untested against your data, so you may need to tweak a few things along the way.

 

[jimdandy1]

yelworcm,

Thank you! That worked perfectly!! I had to tweak it and add the column names to the WITH clause but after that it worked just like I needed and it ran faster too.

Thank you!!

John