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

Transact-SQL
1

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.

Capture
Capture.JPG713×508 61.2 KB

2 
select a.customer
      ,b.service_start_date
      ,b.service_end_date
      ,b.type_of_purchase
      ,a.service_start_date as initial_purchase_date
      ,a.type_of_purchase
  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)
;
3

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

4

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

5

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.

6

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?

7

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

8

hi

i saw the whole post and others comments

i tried to do it

if it helps GREAT
:slight_smile:
:slight_smile:

drop create data .... 
use tempdb 

    go 


    drop table table1
    go 

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

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

    go 

    select * from table1
    go
SQL .. I tried 
SELECT a.cust, 
       a.serv_sd AS initial_purchase_date, 
       a.topu    AS initial_purchase, 
       b.* 
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

Results

image
image.png1128×388 139 KB