How to pull data before and after an event in one table?

How to pull data before and after an event in one table?

hi so i am trying to pull claims data for my customers based on an event. the event is the purchase of a car. so i am trying to pull all customers who purchases a car in 2017 and pull all their purchases that happened 2 months before the service_start_date and 2 months after the

service_end_date of the car purchase. i want to exclude anything that happened over 2 months pre service_start_date and 2 months post.service_end_date

table 1 pictured here is the original table that has all my customers, table 2 is the final product that i need to get to. i also like to create a column in my table 2 saying what was the initial purchase (in this case it's the car) and a column that has the initial purchase date of the car.

i have millions of rows in my table

any help is appreciated.

select a.customer
      ,a.service_start_date as initial_purchase_date
  from table1 as a
       inner join table1 as b
               on b.customer=a.customer
              and b.service_start_date>=dateadd(month,-2,a.service_start_date)
              and b.service_start_date<=dateadd(month,2,a.service_end_date)
 where a.type_of_purchase='Car'
   and a.service_start_date>=cast('2017-01-01' as date)
   and a.service_start_date<cast('2018-01-01' as date)

sounds like homework to me. What have you tried so far. Also, please post ddl and sample data

Hi Mike,

how do I post sample data?

this is what I tried. it works fine but the issue is that date in my table is formatted as varchar and i need to format it as date. so the overlap is not working at this moment...

select t.*
, min(case when type_of_purchase = 'Car' and extract(year from service_start_date) = 2017 then service_start_date end)
over (partition by customer) as purchase_date
, 'Car' as initial_purchase
from table 1 as t
qualify (add_months(purchase_date,-2),add_months(purchase_date,2)) overlaps (service_start_date, service_end_date);

This is not Microsoft SQL Server, so you might get better answers on forum for you database engine (looks like Teradata).

Btw.: on Microsoft SQL Server you can use either convert or cast functions to turn varchar into date, but it doesn't performance is not good.

Thanks Bitsmed. yes this is teradata, the issue i have is that date in my table is formatted as varchar.

can I use your query in teradata? or can I tweak my query by adding cast to the date?

I'm sorry, I don't know. I have never worked with Teradata.


i saw the whole post and others comments

i tried to do it

if it helps GREAT

drop create data ....
use tempdb 


    drop table table1

    create table table1
    cust varchar(100) NULL, 
    serv_sd date NULL, 
    serv_ed date NULL,
    topu varchar(100) NULL

    insert into table1 
    VALUES ( 'A','2017-01-08','2017-01-23','Plane') ,
    ( 'A','2016-11-21','2016-12-01','Truck'),
    ( 'A','2016-06-04','2016-06-06','Boat'),
    ( 'B','2017-01-12','2017-01-27','Plane'),
    ( 'A','2017-01-01','2017-01-05','Car'),
    ( 'B','2016-11-11','2016-12-04','Truck'),
    ( 'B','2017-01-06','2017-01-09','Car')


    select * from table1
SQL .. I tried
SELECT a.cust, 
       a.serv_sd AS initial_purchase_date, 
       a.topu    AS initial_purchase, 
FROM   table1 a 
       JOIN table1 b 
         ON a.cust = b .cust 
            AND a.serv_ed BETWEEN Dateadd(month, -2, b.serv_sd) AND 
                                  Dateadd(month, 2, b.serv_ed) 
WHERE  a.topu = 'CAR'

The order of results looks different( the result is the SAME ) but it can be modified in the SQL