Below is the data in Table1
BUYER_ID | ITEM_ID | CREATED_TIME
-----------+-------------------+------------------------
1345653 110909316904 2012-07-09 21:29:06
1345653 151851771618 2012-07-09 19:57:33
1345653 221065796761 2012-07-09 19:31:48
1345653 400307563710 2012-07-09 18:57:33
1345653 310411560125 2012-07-09 16:09:49
1345653 120945302103 2012-07-09 13:40:23
1345653 261060982989 2012-07-09 09:02:21
Below is the data in Table2
USER_ID | PRODUCT_ID | LAST_TIME
-----------+-------------------+----------------------
1345653 110909316904 2012-07-09 21:30:06
1345653 151851771618 2012-07-09 19:57:33
1345653 221065796761 2012-07-09 19:31:48
1345653 400307563710 2012-07-09 18:57:33
Problem Statement:-
I need to Compare Table2 with Table1 on BUYER_ID and USER_ID. And I need to find the count of mismatch if difference between CREATED_TIME and LAST_TIME is greater than 15 minutes
So if you look at the above example, see the first row in both the tables ITEM_ID and PRODUCT_ID are same but CREATED_TIME and LAST_TIME is not same, and difference between those two times is of only 1 minute. So if difference is greater than 15 minutes then I want to show them as an error. So expected output will be for above case-
BUYER_ID ERROR
1345653 1
First, find all the buyers that have a match:
select *
from table1 t1 join
table2 t2
on t1.buyer_id = t2.user_id and
datediff(min, t1.created_time, t2.last_time) between -15 and 15
Using this, now find the cases where there is no match:
with matches as (
select *
from table1 t1 join
table2 t2
on t1.buyer_id = t2.user_id and
datediff(min, t1.created_time, t2.last_time) between -15 and 15
)
select *
from table1 t1 left outer join
matches m
on t1.buyer_id = m.user_id and
t1.product_id = m.product_id and
t1.created_time = m.created_time
where m.buyer_id is null
Below is the data in Table1
BUYER_ID | ITEM_ID | CREATED_TIME
-----------+-------------------+------------------------
1345653 110909316904 2012-07-09 21:29:06
1345653 151851771618 2012-07-09 19:57:33
1345653 221065796761 2012-07-09 19:31:48
1345653 400307563710 2012-07-09 18:57:33
1345653 310411560125 2012-07-09 16:09:49
1345653 120945302103 2012-07-09 13:40:23
1345653 261060982989 2012-07-09 09:02:21
Below is the data in Table2
USER_ID | PRODUCT_ID | LAST_TIME
-----------+-------------------+----------------------
1345653 110909316904 2012-07-09 21:30:06
1345653 151851771618 2012-07-09 19:57:33
1345653 221065796761 2012-07-09 19:31:48
1345653 400307563710 2012-07-09 18:57:33
Problem Statement:-
I need to Compare Table2 with Table1 on BUYER_ID and USER_ID. And I need to find the count of mismatch if difference between CREATED_TIME and LAST_TIME is greater than 15 minutes
So if you look at the above example, see the first row in both the tables ITEM_ID and PRODUCT_ID are same but CREATED_TIME and LAST_TIME is not same, and difference between those two times is of only 1 minute. So if difference is greater than 15 minutes then I want to show them as an error. So expected output will be for above case-
BUYER_ID ERROR
1345653 1
First, find all the buyers that have a match:
select *
from table1 t1 join
table2 t2
on t1.buyer_id = t2.user_id and
datediff(min, t1.created_time, t2.last_time) between -15 and 15
Using this, now find the cases where there is no match:
with matches as (
select *
from table1 t1 join
table2 t2
on t1.buyer_id = t2.user_id and
datediff(min, t1.created_time, t2.last_time) between -15 and 15
)
select *
from table1 t1 left outer join
matches m
on t1.buyer_id = m.user_id and
t1.product_id = m.product_id and
t1.created_time = m.created_time
where m.buyer_id is null
0 commentaires:
Enregistrer un commentaire