sql duration around the reference date

Question

There are a pool of customers in the table customer with some activities and each customer has perfomed a specific activity and it has been marked as an event occured and the date has been selected as reference date.

Now the task is to find records of the customer around the reference date. for example. select the activities of each customer 10 days before event occured, or 20 days after event occured or 5days before and 5days after.

table looks like:

Customer ID| activities | date
 1          | a1         | date1
 1          | a2         | date1
 1          | a3         | date2
 1          | a1         | date2
 .
 1          | a-sp       | date22 ---> a-sp is an event occurred and the date is Ref. Date
 .
 1          | a1000      | date30

like wise for many other customers

any suggestions from the community?

Mention database/version, sample data, your expected output and if you have tried anything so far. — Utsav, May 31 '17 at 09:51

score 2 · Accepted Answer · answered May 31 '17 at 10:59

2

You can do something like this:

select t.*, e.event_date
from t join
     (select t.customerid, t.date as event_date
      from t
      where t.activity = 'a-sp'
     ) e
     on t.date >= add_days(e.event_date, -5) and
        t.date <= add_days(e.evant_date, 5);

answered May 31 '17 at 10:59

Gordon Linoff

1,242,037
58
646
786

Thanks Gordon! its helped! – sveer Jun 01 '17 at 09:58

sql duration around the reference date

1 Answers1