SQL - remove duplicates based on date range

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!
robthomas09Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
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
 
robthomas09Author Commented:
Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.