Trying to retrieve most recent value

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)

Trying to retrieve most recent value

Author

Topic

dmorand
Starting Member

20 Posts

Posted - 2009-01-26 : 13:50:38

I've got this query where I'm retrieving some medication orders on a patient, and I want to retrieve their most recent INR value.

I have this query below, but I need to modify it so that I am only retrieving the most recent observationdatetime value from the hinvestigationresult table for the patient.


select distinct a.orderaswritten as 'Drug Name',
	a.medorderstatusmodifier as 'Order Status',
	a.internalid as 'Ancillary Number',
	ir.observationdatetime as 'Observation Date/Time',
	ir.resultValue as 'INR Value'
from 
	vw_ORE_MedOrder a with (nolock)
	left outer join hinvestigationresult ir with (nolock)
	on a.patient_oid = ir.patient_oid 
		and ir.findingabbreviation = 'INR'
		and ir.signed = 1
where a.patient_oid = '1681817'
and a.orderaswritten like '%Coumadin%'

I tried the query below, but I'm getting NULL values in the ir.resultValue and ir.observationdatetime fields. I'd think this would work.


select distinct a.orderaswritten as 'Drug Name',
	a.medorderstatusmodifier as 'Order Status',
	a.internalid as 'Ancillary Number',
	ir.observationdatetime as 'Observation Date/Time',
	ir.resultValue as 'INR Value'
from 
	vw_ORE_MedOrder a with (nolock)
	left outer join 
	(select top 1 patient_oid,observationdatetime,resultvalue
		from hinvestigationresult ir with (nolock)
		where ir.findingabbreviation = 'INR'
		and ir.signed = 1
		order by observationdatetime desc) as ir
	on a.patient_oid = ir.patient_oid 
where a.patient_oid = '1681817'
and a.orderaswritten like '%Coumadin%'

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2009-01-26 : 14:30:13

Could you use the MAX function? So, instead of the TOP 1 and the ORDER BY observationdatetime DESC, maybe you could try to use the function MAX(observationdatetime)?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-26 : 22:47:31

Maybe this:


select  a.orderaswritten as 'Drug Name',
	a.medorderstatusmodifier as 'Order Status',
	a.internalid as 'Ancillary Number',
	Coalesce(Max(ir.observationdatetime),...) as 'Observation Date/Time',
	Coalesce(ir.resultValue,...) as 'INR Value'
from 
	vw_ORE_MedOrder a with (nolock)
	left outer join hinvestigationresult ir with (nolock)
	on a.patient_oid = ir.patient_oid 
		and ir.findingabbreviation = 'INR'
		and ir.signed = 1
where a.patient_oid = '1681817'
and a.orderaswritten like '%Coumadin%'
Group by a.orderaswritten ,
	a.medorderstatusmodifier ,
	a.internalid,Coalesce(ir.resultValue)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 00:21:27

[code]
select distinct a.orderaswritten as 'Drug Name',
a.medorderstatusmodifier as 'Order Status',
a.internalid as 'Ancillary Number',
ir.observationdatetime as 'Observation Date/Time',
ir.resultValue as 'INR Value'
from
vw_ORE_MedOrder a with (nolock)
outer apply
(select top 1 patient_oid,observationdatetime,resultvalue
from hinvestigationresult ir with (nolock)
where a.patient_oid = ir.patient_oid
and ir.findingabbreviation = 'INR'
and ir.signed = 1
order by observationdatetime desc) as ir
where a.patient_oid = '1681817'
and a.orderaswritten like '%Coumadin%'
[/code]

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-27 : 02:24:27

select distinct a.orderaswritten as 'Drug Name',
a.medorderstatusmodifier as 'Order Status',
a.internalid as 'Ancillary Number',
ir.observationdatetime as 'Observation Date/Time',
ir.resultValue as 'INR Value'
from
vw_ORE_MedOrder a with (nolock)
left outer join
(select top 1 patient_oid,observationdatetime,resultvalue
from hinvestigationresult ir with (nolock)
where ir.patient_oid = '1681817' AND ir.findingabbreviation = 'INR'
and ir.signed = 1
order by observationdatetime desc) as ir
on a.patient_oid = ir.patient_oid
where a.patient_oid = '1681817'
and a.orderaswritten like '%Coumadin%'

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources