Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-11-02
4
Medium Priority
?
60 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 37

Accepted Solution

by:
Pawan Kumar earned 2000 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 37

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

810 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