Solved

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

Posted on 2016-11-02
4
46 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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