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'
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?
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.
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'