CMCITD
asked on
SQL Select Distinct on one column--but as it applies to each patient in the select statemtn
Here is my query;
select Name, rx.RowUpdateDateTime, rx.VaccineName, ap.PrimaryCareID from AdmVisits AV
left join PhaRxImmunizationData rx
on AV.VisitID = rx.VisitID
LEFT JOIN AdmProviders ap
ON AV.VisitID = ap.VisitID
where rx.VaccineName is not null and
ap.PrimaryCareID IN (
'PHYSICIAN1', 'PHYSICIAN2', 'PHYSICIAN3', 'PHYSICIAN4'
)
order by ServiceDateTime desc
When I run this, I get the following;
Name RowUpdateDateTime VaccineName PrimaryCareID
PATIENT 1 2016-10-31 22:43 Tetanus Physican1
PATIENT 1 2016-10-31 22:42 Tetanus Physican1
PATIENT 2 2016-10-30 19:43 Pneumococcal Physican1
PATIENT 2 2016-10-30 19:43 Pneumococcal Physican1
I can tell the system is updating the immunization twice--probably due to a workflow with the nurse, etc. I don't really care to have it twice though, I just want a single list of each unique immunization the patient received. So, I'm stuck on getting that distinct select to work ONLY on the immunization name. I've tried a variety of group by, etc. with no luck. Thanks for any help!
select Name, rx.RowUpdateDateTime, rx.VaccineName, ap.PrimaryCareID from AdmVisits AV
left join PhaRxImmunizationData rx
on AV.VisitID = rx.VisitID
LEFT JOIN AdmProviders ap
ON AV.VisitID = ap.VisitID
where rx.VaccineName is not null and
ap.PrimaryCareID IN (
'PHYSICIAN1', 'PHYSICIAN2', 'PHYSICIAN3', 'PHYSICIAN4'
)
order by ServiceDateTime desc
When I run this, I get the following;
Name RowUpdateDateTime VaccineName PrimaryCareID
PATIENT 1 2016-10-31 22:43 Tetanus Physican1
PATIENT 1 2016-10-31 22:42 Tetanus Physican1
PATIENT 2 2016-10-30 19:43 Pneumococcal Physican1
PATIENT 2 2016-10-30 19:43 Pneumococcal Physican1
I can tell the system is updating the immunization twice--probably due to a workflow with the nurse, etc. I don't really care to have it twice though, I just want a single list of each unique immunization the patient received. So, I'm stuck on getting that distinct select to work ONLY on the immunization name. I've tried a variety of group by, etc. with no luck. Thanks for any help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi CMCITD,
Have you tried the above approaches?
Regards,
Have you tried the above approaches?
Regards,
ASKER
Sorry for the delay in credit--thank you!
Could you use something like:
Open in new window