?
Solved

SQL - remove duplicates based on date range

Posted on 2013-02-05
2
Medium Priority
?
464 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 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

752 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