Getting most recent data - 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

Getting most recent data

Author

Topic

dseb
Starting Member

7 Posts

Posted - 2012-02-20 : 20:42:25

Hi,
i've been trying to get the latest data of patients on my list but my code just wont do so.
Here's my query:

select distinct cs.CreationTime, cs.LastName+ ','+cs.FirstName as 'Pt Name', cs.PatientAccountID, hpi.Value as MR#,
case cs.RelationType
WHEN 0 THEN 'Attending Doctor'
WHEN 1 THEN 'Primary Physician'
WHEN 2 THEN 'Referring Doctor'
WHEN 3 THEN 'Consulting Doctor'
WHEN 4 THEN 'Admitting Doctor'
WHEN 5 THEN 'Operating Doctor'
WHEN 6 THEN 'Discharging Doctor'
WHEN 7 THEN 'Responsible Doctor'
WHEN 8 THEN 'Death Declared Doctor'
WHEN 9 THEN 'Other Care Giver'
WHEN 10 THEN 'Additional Doctor'
WHEN 11 THEN 'Death Approved Doctor'
WHEN 12 THEN 'Covering Doctor'
WHEN 13 THEN 'Census'
WHEN 14 THEN 'Nurse Assignment'
ELSE CAST(cs.RelationType AS varchar) END AS Relation
, DATEDIFF("MINUTE",cs.CreationTime,getdate()) as 'XY Minutes'

from dbo.HCensusPatientList cs with (nolock)
LEFT OUTER JOIN dbo.HStaff st with (nolock)
ON cs.StaffOID = st.ObjectID
LEFT OUTER JOIN dbo.HPatient P with (nolock)
ON cs.PatientOID = P.ObjectID
LEFT OUTER JOIN dbo.HPatientIdentifiers hpi with (nolock)
on hpi.Patient_oid = P.ObjectID

Where hpi.EntityOID = 202
and st.ObjectID = 802204
;

I tried using the max function based on the "creation time" but i still get a history of a patient's info. How can i modify the code above to only include data with the most recent "creation Time".

Thank you.

dSEB

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-20 : 20:49:38

What table is that in, and PLEASE who told you to use nolock?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

dseb
Starting Member

7 Posts

Posted - 2012-02-20 : 21:21:50

it's common practice to use nolock where I work. So i just picked it up from from there. But if you want to know which table the CreationTime is coming from it's the "HCensusPatientList" table

dSEB

Jan Novak
Starting Member

6 Posts

Posted - 2012-02-21 : 06:35:01

Select Top 10
...
Order by CreationTime Desc

Please also read about nolock: http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-21 : 06:42:02

See - [url]http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx[/url]

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources