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

Transact-SQL
1

Final%20product

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.

2

it would be appreciated if you provided sample data as follows.

create table #miguelflor(customer varchar(15),
                         service_start_date date,
						 service_end_date date,
						 purchase_type varchar(15))

insert into #miguelflor
select 'A', '2017-01-01', '2017-01-05', 'Car' union
--etc
3

Thanks! how can I provide the data? i am only allowed to post images..

how is the query filtering on the customers who purchased a car and how it's pulling all claims 2 months pre and post including the purchase of a car?
Mike

4

I went ahead and provided your sample data (create table and insert statement(s)).

Try:

if OBJECT_ID('tempDb..#table_1') is not null drop table #table_1;

go

create table #table_1 
(
[customer] varchar(20),
[service start date] date,
[service end date] date,
[type of purchase] varchar(10)

)


insert #table_1 
values
('A','1/1/2017','1/5/2017','Car'),
('A','11/21/2016','12/1/2016','Truck'),
('A','1/8/2017','1/23/2017','Plane'),
('A','6/4/2016','6/6/2016','boat'),
('B','1/6/2017','1/9/2017','Car'),
('B','11/11/2016','12/4/2016','Truck'),
('B','1/12/2017','1/27/2017','Plane');

go

select  b.*
,a.[type of purchase] [Original Purchase]
from 
 #table_1 a  
 , #table_1 b
where 
b.customer = a.customer 
and a.[type of purchase] = 'Car'
and 
(
b.[service start date] <= dateadd(month, 2, a.[service start date] ) and b.[service start date] >= a.[service start date] 
or
a.[service start date] >= b.[service start date] and a.[service start date] <= dateadd(month, 2,b.[service start date])
)
	


drop table #table_1;
5

Thanks, i have million of customers in table 1. i can't insert manually into table1. any other solutions?

6

I'm not totally clear what you mean but you would need to change the sample table names in the above query to your actual table name and update the column names respectively.

 #table_1 a  
 , #table_1 b

Thanks!

7

in order to provide you an answer we need sample data. without sample data it would be a wild guess and we could end up giving you wrong answer. what femiolan did was provide some sample data to work out the final solution
so now you need to replace the sample table creation (DDL) and insertion (DML) with your real table
so change #table_1 to the table that is your database.

8

great but i don't want to insert manually
('A','1/1/2017','1/5/2017','Car'),
('A','11/21/2016','12/1/2016','Truck'),
('A','1/8/2017','1/23/2017','Plane'),
('A','6/4/2016','6/6/2016','boat'),
('B','1/6/2017','1/9/2017','Car'),
('B','11/11/2016','12/4/2016','Truck'),
('B','1/12/2017','1/27/2017','Plane');

what i wanted to do is pull all claims including the car purchase and any claims that fall within 60 ays pre and post service start date.

9

you dont have to, that is just the sample data :thinking: just use his final query using your table