Solved

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

Posted on 2016-11-02
4
53 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

740 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