Notifications
Clear all
Topic starter
Creating a query and it is bringing in 2 results for patients, showing race and the other showing race2. I only want the 1 patient with the main race
join PatientRace as pr on pr.pid = pp.pid
join medlists as mlr on mlr.MedListsId = pr.PatientRaceMid
Posted : November 22, 2023 1:32 am
I use row_number syntax if I want to isolate a value. This row_number is based on creation date and assuming "Race 1" dropdown is selected first. I couldn't see how else they designated race order if more than one exists. *Using SSMS SQL*
SELECT DISTINCT
*
FROM(
SELECT DISTINCT
P.PATIENTID AS CHARTID
,P.LAST AS LNAME
,P.FIRST AS FNAME
,P.BIRTHDATE AS DOB
,M1.DESCRIPTION AS RACE1
,ROW_NUMBER() OVER (PARTITION BY PR.PID ORDER BY PR.CREATED) AS RACERN
FROM
CENTRICITYPM.DBO.PATIENTPROFILE P
LEFT JOIN CENTRICITYPM.DBO.PATIENTRACE PR ON PR.PID = P.PID
LEFT JOIN CENTRICITYPM.DBO.MEDLISTS M1 ON M1.MEDLISTSID = PR.PATIENTRACEMID
) Q1
WHERE
RACERN = 1
ORDER BY
CHARTID
Posted : November 22, 2023 10:21 am
Topic starter
Thank you. that worked.
Posted : November 27, 2023 2:38 am
