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

Enthusiast

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

Hi Waidar, i am using that below query to pull claims before and after a specefic event.

 

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

i am getting an error in teradata  saying:  invalid operands to overlaps operator.

 

any help is appreciated

 

 

Teradata Employee

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

OVERLAPS works with the datatype PERIOD which itself use DATE or TIME or TIMESTAMP datatypes.

Last time you had issue with your dates (service_start_date, service_end_date), maybe it's the same ?

Enthusiast

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

so the service start date and service end date are formatted as Varchar.

 

do you think this is the issue? if yes how do i go about fixing it?

 

you are very helpful, thanks for getting back to me.

 

Mike

Teradata Employee

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

Cast them as date, using format or to_date function.

Enthusiast

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

I tried this but didn't work. my date is formatted as varchar 20: yyyy-mm-dd. your query works great it;s jut the overlap didn't work maybe because the date issue. anyway you can tweak that query below ? (i think my cast funtion is wrong)

 

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

 

 

Teradata Employee

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

Try this one :

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