Can someone please help. I receive the multipart identifier error message on Language.LanguageID of this query.
SELECT DISTINCT
--Appointments.ApptStart
--DoctorFacility.ListName
pp.searchname
, pp.Sex
, pp.Birthdate
, PP.Zip
, DATEDIFF(HOUR,pp.Birthdate,'2014-12-31')/8766 as Age
, PatientInsurance.InsuredId
, pp.LanguageId
, MAX(CASE WHEN race.Description IN ('Unspecified','Black or African American') THEN
CASE WHEN subrace.description = 'African American' THEN subrace.Description
WHEN subrace.description IN ('Unknown','Undetermined') THEN 'Unknown'
ELSE subrace.description END
ELSE race.Description END) AS race /* Only using 1 race for now */
, eth.Description
FROM CentricityPS.dbo.Appointments Appointments
INNER JOIN CentricityPS.dbo.DoctorFacility DoctorFacility
ON Appointments.ResourceId=DoctorFacility.DoctorFacilityId
LEFT OUTER JOIN CentricityPS.dbo.PatientProfile pp
ON Appointments.OwnerId=pp.PatientProfileId
LEFT OUTER JOIN CentricityPS.dbo.MedLists ApptStatus
ON Appointments.ApptStatusMId=ApptStatus.MedListsId
LEFT OUTER JOIN CentricityPS.dbo.PatientInsurance PatientInsurance
ON pp.PatientProfileId=PatientInsurance.PatientProfileId
LEFT OUTER JOIN PatientProfile PatientProfile
ON pp.LanguageId=Language.LanguageId
LEFT JOIN MedLists eth
ON pp.EthnicityMId = eth.MedListsId
LEFT JOIN PatientRace pr
ON pp.PId = pr.pid
LEFT JOIN MedLists race
on pr.PatientRaceMid = race.MedListsId
LEFT OUTER JOIN dbo.medlists subrace
ON pr.PatientRaceSubCategoryMid = subrace.medlistsID
WHERE
PatientInsurance.InsuredId LIKE 'NHP%')
Do you have a table in your database called Language? If there is, that table needs to be listed in the FROM clause. How that table relates to the other tables that you have now depends on what columns you have in that table, and the logic to be used to relate them to existing tables.
You said want to relate PatientProfile.LanguageID to Language.LanguageID. That connects those two tables. But how does that pair connect to other tables? When you have a join condition, it has to refer to the tables that you have listed previously in the FROM clause.
So I have changed the code a little. Now I receive an error message : Incorrect syntax near CASE.
SELECT Distinct PatientProfile.First,
PatientProfile.Last,
PatientProfile.Zip,
PatientProfile.Birthdate,
PatientInsurance.InsuredId,
Language.Code
MAX(CASE WHEN race.Description IN ('Unspecified','Black or African American') THEN
CASE WHEN subrace.description = 'African American' THEN subrace.Description
WHEN subrace.description IN ('Unknown','Undetermined') THEN 'Unknown'
ELSE subrace.description END
ELSE race.Description END) AS race /* Only using 1 race for now */
, eth.description
FROM PatientProfile INNER JOIN
PatientInsurance ON PatientProfile.PatientProfileId = PatientInsurance.PatientProfileId
INNER JOIN
PatientRace ON PatientProfile.PatientProfileId = PatientRace.PatientProfileId AND PatientProfile.PId = PatientRace.PID
INNER JOIN
Language ON PatientProfile.LanguageId = Language.LanguageId
LEFT JOIN MedLists eth
ON pp.EthnicityMId = eth.MedListsId
inner JOIN PatientRace pr
ON pp.PId = pr.pid
inner JOIN MedLists race
on pr.PatientRaceMid = race.MedListsId
inner JOIN dbo.medlists subrace
ON pr.PatientRaceSubCategoryMid = subrace.medlistsID
where PatientInsurance.InsuredId like 'NHP%'
group by PatientProfile.Last