SQL - remove duplicates based on date range
Posted on 2013-02-05
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:
If today is 02052013 then I would only want to return the most recent value
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:
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?
select person_nbr, MAX(convert(datetime,appointment_date, 102)) as Appt_Date
group by appointment_date person_nbr
having MAX(convert(datetime,appointment_date, 102)) < GETDATE() -30