Solved

SQL - remove duplicates based on date range

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

856 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