Solved

SQL Select Distinct on one column--but as it applies to each patient in the select statemtn

Posted on 2016-11-02
4
49 Views
Last Modified: 2016-11-29
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!
0
Comment
Question by:CMCITD
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 41870559
Are you basically getting duplicates because of the UpdatedDateTime column?

Could you use something like:
select Name, max(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'
                              )
group by Name, rx.VaccineName, ap.PrimaryCareID
order by ServiceDateTime desc

Open in new window

0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41870592
Try..

SELECT DISTINCT Name, MAX(rx.RowUpdateDateTime) OVER (PARTITION BY Name,rx.VaccineName, ap.PrimaryCareID) 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

Open in new window


Also do you need order by ServiceDateTime desc ?

Hope it helps..
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41871568
Hi CMCITD,

Have you tried the above approaches?

Regards,
0
 

Author Closing Comment

by:CMCITD
ID: 41905920
Sorry for the delay in credit--thank you!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question