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

General
Enthusiast

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 and after the original purchase of the car. i want to exclude anything that happened over 2 months pre and post.

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 original purchase (in this case it's the car)

any help is appreciated.

Final%20product


Accepted Solutions
Teradata Employee

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

Well, as dnoeth specified if it's just a constant you can add it to you query :

 

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 table1 as t qualify (add_months(purchase_date,-2),add_months(purchase_date,2)) overlaps (service_start_date, service_end_date);

 Or you can go for a popup :

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

 

 

 

1 ACCEPTED SOLUTION
12 REPLIES
Junior Contributor

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

You can find the start date using CASE and then apply OVERLAPS to get the +/- two months like this

select ...
   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
from tab
qualify
(add_months(purchase_date,-2),add_months(purchase_date,2)) overlaps (service_start_date, service_end_date)

As you look for cars you can hardcode the original_purchase as 'car', otherwise use

first_value(case when extract(year from service_start_date) = 2017 then type_of_purchase end ignore nulls)
over (partition by customer order by service_start_date)

 

Enthusiast

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

thanks.I uploaded my original table to my data lab and ran the query you wrote but nothinh came back. the only thing that came back is a purchase date field. any feedback is appreciated.

Teradata.JPG

Junior Contributor

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

My Select is untested, but the logic should be right.

Can you provide your example data as Inserts instead of a picture?

 

Of course you need to add more columns to your Select.

Teradata Employee

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

I did test your code and it works fine :

create multiset volatile table table1, no log
( customer              char(1)
, service_start_date    date
, service_end_date      date
, type_of_purchase      varchar(10)
)
primary index (customer)
on commit preserve rows;

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

collect statistics column (customer) on table1;

 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
   from table1 as t
qualify (add_months(purchase_date,-2),add_months(purchase_date,2)) overlaps (service_start_date, service_end_date);   
Enthusiast

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

Thanks Waldar, my table has million of customers, i can't insert the values in table1 manually. any solution to avoid that?

Teradata Employee

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

table1 is just a representation of your data as mentionned in the original post.

 

 

It's goal is to allow anyone to test any solution with a simple copy / paste.

 

You just have to replace table1 by the name of your table / view and columns name also if needed.

 

Enthusiast

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

got it!

so assuming table1 exists in my database and i am only intersted in pulling all car purchases and any purchase that happened 2 months pre and 2 months post service start date of the initial car purchase, my query would be something like that:

 

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
   from table1 as t
qualify (add_months(purchase_date,-2),add_months(purchase_date,2)) overlaps (service_start_date, service_end_date);

 correct? what is t.* stand for in your select statement?

 

my final result that I like to get to is pictured in my original post (second table) where i pulled all car purchases, any purchase that happened 2 months  pre and post and i added a column saying what the original purchase was. does it make sense?

 

thanks for the help, really appreciate it

 

 

Tags (1)
Enthusiast

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

Hi. so i pasted the query you provided in Teradata and the results didn't come back. my table 1 is the original table i have in my database. so i queried of that table.

 

Query in teradata.JPG                                                                                        this is my table 1 in the database:

1.JPG                                                                                                                                                                                                             this is the end result that i like to get to:

 

2.JPG   

really appreciate all the help

Teradata Employee

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

Well, if you have no answer it is you don't have the case you described inside your table.

Can you post the show table ?

Maybe you have datatypes issues with dates.