Solved

SQL - remove duplicates based on date range

Posted on 2013-02-05
2
450 Views
Last Modified: 2013-02-14
Hello experts,

I have a table that keeps values of patient appointments.  

I am trying to get a list of patient names that have had an appointment in the last 30 days so I can send them a survey.  However, I dont want to send them a survey again for another 30 days, so if they have yet another appointment 1 week after their survey, I dont want to send them another one immediately because only one week will have passed since they received a survey.

So, if I have a table that houses:

patient_appointments

person_nbr    appt_date      
123421          02042013      
123421          0102012        
123421          12142012      
123421          03072011    

If today is 02052013 then I would only want to return the most recent value
person_nbr    appt_date      
123421          02042013      

I need to load that value into an xref table that makes sure I dont send that person another survey for a nother 30 days, so I load that row into an xref table:

survey_xref
person_nbr    appt_date      
123421          02042013  

So then next time I run my query to get my names, I simply include the survey_xref table in my join, where survey_xref.appt_date < getdate()-30?

something like:

select person_nbr, MAX(convert(datetime,appointment_date, 102)) as Appt_Date
from patient_appointments
into survey_xref
group by appointment_date person_nbr
having MAX(convert(datetime,appointment_date, 102)) < GETDATE() -30

Thoughts?

Thanks!
0
Comment
Question by:robthomas09
[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 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 38856156
Below is the query to find patients who have had appointments within the past 30 days, and when their last appt in that time period was, and load that to the new table:


SELECT
    GETDATE() AS survey_date, pa.person_nbr, MAX(pa.appointment_date) as appt_date
FROM dbo.patient_appointments pa
INTO dbo.survey_xref
WHERE
    pa.appointment_date >= DATEADD(DAY, -30, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
GROUP BY
    pa.person_nbr
0
 

Author Closing Comment

by:robthomas09
ID: 38890427
Thanks!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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.
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.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the adminiā€¦

749 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