SELECT p.LastName AS 'Last Name', p.FirstName AS 'First Name', p.MInitial AS 'MI', 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 = 'Referral') ORDER BY o.doOrder, 'Referral Date'
This displays the LastName, FirstName, and MInitial as separate fields with corresponding headers. I need them to display as one field with the header Patient.
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 = 'Referral') ORDER BY o.doOrder, 'Referral Date'
To avoid null concatenation nulling the expression:
SELECT
p.LastName + ', ' + 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 = 'Referral'
ORDER BY
o.doOrder
, Referrals.ReferralDate;Date;