Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

SQL 2005 - Using Top 1

I am trying to find the last Referring MD's name for a patient and cannot quite get it. When I leave in the ORDER BY, it looks at the last Visit I entered and because that was NULL, it tells me NULL. I had a visit prior to that that contained the MD's name and thats the one I want to see.

Example

DOS 12/29/2009 - No Referring MD choosen on Patient Visit.
DOS 12/28/2009 - Dr Jones choosen as the Ref MD

How can I code this to pull the last none NULL MD from my table. If the end result is no Ref MD choosen on any Visit, go ahead and give me the NULL.
SELECT TOP 1
        '250.LastVisitReferringMD' = ref.ListName
  
  FROM
        PatientVisit pv 
		LEFT JOIN DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId 
		INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
  
  WHERE
        pp.PatientProfileId = '48'
  
--  ORDER BY
--		pv.Visit desc

Open in new window

0
Jeff S
Asked:
Jeff S
3 Solutions
 
EmesCommented:
can you just do

WHERE
        pp.PatientProfileId = '48'   and

 ref.ListName is not null
 
       
0
 
mabbj747Commented:
Append the NOT NULL query

SELECT TOP 1
        '250.LastVisitReferringMD' = ref.ListName
 
  FROM
        PatientVisit pv
            LEFT JOIN DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
            INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
 
  WHERE
        pp.PatientProfileId = '48'
AND
        ref.ListName IS NOT NULL
ORDER BY
            pv.Visit desc
0
 
tsqlguyCommented:
SELECT TOP 1 ISNULL(ref.ListName, 'None') AS 'LastReferringMD'  
FROM PatientVisit pv
LEFT JOIN DoctorFacility ref ON pv.ReferringDoctorId = ref.DoctorFacilityId
INNER JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId  
WHERE pp.PatientProfileId = '48' AND ref.DoctorFacilityID IS NOT NULL

Use the IsNull Function to provide a value when what you're looking for is null.
0
 
Jeff SAuthor Commented:
THANKS. Split to be fair and all helped.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now