Query When Null Values Are Present

1

I got this query from one of you and it is working great. Now I need to use it with slightly different data. I have some old records that do not have a value in the field p.GraduatingYear. Can I add something to this query so that it will pull all the other data when this field is null?

SELECT p.LastName + ', ' + p.FirstName + ' ' + p.MInitial AS 'Patient', p.GraduatingYear AS 'Graduating Year', Referrals.ReferralDate AS 'Referral Date', p.PatientID FROM tblDimPatients AS p LEFT OUTER JOIN (SELECT PatientID, MIN(ReferralDate) AS ReferralDate FROM tblFactReferrals GROUP BY PatientID) AS Referrals ON p.PatientID = Referrals.PatientID LEFT OUTER JOIN tblDropOptions AS o ON o.doOption = p.GraduatingYear WHERE (p.Status = 'Inactive') ORDER BY o.doOrder, 'Referral Date'

Thank you.

2

The way the query is written now, you should get all the rows in the tblDimPatients as long as the Status column in that table has the value 'Inactive'. Is that not what you are getting? Do you see no records when the GraduatingYear column has a null value?

3

I am getting all the records but no Patient name when the p.GraduatingYear is null.

4

Run the query with just the columns from the tblDimPatients and see what you get. You may need to use COALESCE as shown below in case some of the Middle Initials (or other parts of the name are null).

SELECT  COALESCE(p.LastName,'') + ', ' 
                  + COALESCE(p.FirstName,'') + ' ' 
                  + COALESCE(p.MInitial,'') AS 'Patient' ,
        p.GraduatingYear AS 'Graduating Year' ,
        --Referrals.ReferralDate AS 'Referral Date' ,
        p.PatientID
FROM    tblDimPatients AS p
WHERE   ( p.Status = 'Inactive' )

If that shows all the rows you are expecting, then add back the rest of the query.

5

Most likely the middle initial is null. But it's safest to check all the names:

SELECT ISNULL(p.LastName + ', ', '') + ISNULL(p.FirstName + ' ', '') + ISNULL(p.MInitial, '') AS 'Patient', 
    p.GraduatingYear AS 'Graduating Year', Referrals.ReferralDate AS 'Referral Date', p.PatientID 
FROM tblDimPatients AS p 
LEFT OUTER JOIN (SELECT PatientID, MIN(ReferralDate) AS ReferralDate FROM tblFactReferrals GROUP BY PatientID) AS Referrals ON p.PatientID = Referrals.PatientID 
LEFT OUTER JOIN tblDropOptions AS o ON o.doOption = p.GraduatingYear 
WHERE (p.Status = 'Inactive') 
ORDER BY o.doOrder, 'Referral Date'
1 Like
6

The Coalesce feature worked well, except I did not know how to add the Referral date back in. Scott's solution did the trick.

Thanks to all of you.