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

General
Enthusiast

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

i had issue with the date. i fixed it. it's working great. below is a screenshot after i ran your query. one last question, how do i add a column saying what the initial purchase was. so i want to add a column saying what the original purchase was to that table and i want to name that column "initial purcahse" and in this case it would be car.

final result 2.JPG

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);

 

 

 

Enthusiast

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

This is perfect! Thanks Waldar

Mike