CODEWITHSUNDEEP
sqlsql-server-2008-r2
Yikes
Yikes

Reputation: 13

T-SQL Finding Accounts With Contact Dates within 30 days

I have a table that has the following structure:

    Account_No   Contact Date
    -------------------------
    1            2013-10-1
    2            2013-9-12
    3            2013-10-15
    3            2013-8-1
    3            2013-8-20
    2            2013-10-25
    4            2013-9-12
    4            2013-10-2

I need to search the table and return any account numbers that have two contact dates that are within 30 days of each other. Some account numbers may have 5 or 6 contact dates. I essentially just need to return all of the full account numbers and the records that are within 30 days of each other and ignore the rest. Contact date is being stored as a date data type.

So for example account number 3 would return the 2013-8-1 and the 2013-8-20 records, and both of the records for account number 4 would appear as well, but not the other account number records nor the account number 3 from 2013-10-15.

I am using SQL Server 2008 R2.

Thanks for any help in advance!

Upvotes: 0

Views: 723

Answers (3)

Ryan
Ryan

Reputation: 8005

You can use DATEADD for the +/-30 days and compare against the time window:

DECLARE @ContactDates TABLE (
        Account_No  int
    ,   Contact     Date
)

-- Sample data
INSERT @ContactDates (Account_No, Contact)
VALUES
        (1,            '2013-10-01')
    ,   (2,            '2013-09-12')
    ,   (3,            '2013-10-15')
    ,   (3,            '2013-08-01')
    ,   (3,            '2013-08-20')
    ,   (2,            '2013-10-25')
    ,   (4,            '2013-09-12')
    ,   (4,            '2013-10-02')

-- Find the records within +/-30 days
SELECT  c1.Account_No, c1.Contact AS Contact_Date1
FROM    @ContactDates AS c1
   JOIN (
        -- Inner query with the time window
        SELECT  Account_No
            ,   Contact
            ,   DATEADD(dd, 30, Contact) AS Date_Max
            ,   DATEADD(dd, -30, Contact) AS Date_Min
        FROM    @ContactDates
    ) AS c2
        -- Compare based on account number, exclude the same date
        -- from comparing against itself. Usually this would be
        -- a primary key, but this example doesn't show a PK.
        ON (c1.Account_No = c2.Account_No AND c1.Contact != c2.Contact)
-- Compare against the +/-30 day window
WHERE   c1.Contact BETWEEN c2.Date_Min AND c2.Date_Max

This returns the following:

Account_No Contact
========== ==========
3          2013-08-20
3          2013-08-01
4          2013-10-02
4          2013-09-12

Upvotes: 2

T I
T I

Reputation: 9943

Is there an id for each of these records? if so you wont need to create one like i did but based off the data you posted

With cte as
(
    Select *, 
        row_number() over (order by contact_date) id
    From tbl
)

Select *
From cte b
Where exists (
    Select 1
    From cte a
    Where a.account_no = b.account_no
    And a.id <> b.id
    And a.contact_date between b.contact_date and dateadd(d, 30, b.contact_date)
 )

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270583

In SQL Server 2012, you would have the lag() and lead() functions. In 2008, you can do the following for values that are in the same calendar month:

select distinct account_no
from t t1
where exists (select 1
              from t t2
              where t1.account_no = t2.account_no and
                    datediff(month, t1.ContactDate, t2.ContactDate) = 0
             )

There is a bit of a challenge in defining what a "month" is when dates are in different months. (Is March 16 "one month" after Feb 15? They are closer in time than Jan 1 and Jan 31.) You could just go with 30 days:

select distinct account_no
from t t1
where exists (select 1
              from t t2
              where t1.account_no = t2.account_no and
                    datediff(day, t1.ContactDate, t2.ContactDate) <= 30
             )

Upvotes: 1

Related Questions