Solved

SQL - remove duplicates based on date range

Posted on 2013-02-05
2
445 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
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
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 video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
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…

803 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