Home » SQL & PL/SQL » SQL & PL/SQL » Transaction 2 happen within 60 days of Transaction 1 (Oracle Database 12c Enterprise Edition 12.1.0.2.0)
Transaction 2 happen within 60 days of Transaction 1 [message #668882] |
Tue, 20 March 2018 10:33 |
|
Saz
Messages: 8 Registered: March 2018
|
Junior Member |
|
|
Hi Guys,
Relative newbie here .. I'm trying to write a query where I have a list of transactions and I need to see if a different transaction type didn't happened after 60 days of it
E.g. A
Trans 1 : 1/1/18
Trans 2 : 1/11/17, 1/12/17, 2/1/18, 1/2/18, 1/3/18, 17/3/18
Did a trans 2 occurs within 60 days post trans 1 : Y
--> I don't want to return this example
E.g. B
Trans 1 : 1/1/18
Trans 2 : 1/11/17, 1/12/17, 31/12/17, 17/3/18, 21/3/18
Did a trans 2 occurs within 60 days post trans 1 : N
--> I want to return this example
...
SELECT * FROM
(SELECT e.ID, sa.id ** LIST OF TRANSACTIONS #1*
FROM table1 e,
table3 sa
WHERE e.type_id = 'ABC'
AND sa.type_id = 'PRE'
) S1,
(SELECT sa.id, adj.date AS vdate ** LIST OF TRANSACTIONS #2**
FROM table4 sa,
table5 adj
WHERE sa.id = adj.id
) S2
WHERE S1.id = S2.id
and s2.vdate > S1.E_DT
** ME TRYING TO COMPARE IF 1 date of TRANS 2 is after 60 days of TRANS 1**
and s2.vdate < S1.E_DT + 60
and s2.vdate not between (S1.E_DT) and (S1.E_DT + 60 )
Trans #2 will have a large range of dates in the past and in the future of trans #1.
From my googling I think I need a case option or possible a not exists ?
Can you please point me in the right direction ?
Thanks
Sarah
|
|
|
|
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668884 is a reply to message #668883] |
Tue, 20 March 2018 11:41 |
|
Saz
Messages: 8 Registered: March 2018
|
Junior Member |
|
|
Hi Blackswan - sorry I forgot to comment one out after my testing ...
I've attached a table mock up ..
and updated the above request a little
SELECT * FROM
(SELECT e.ID, sa.id , e.dt ** LIST OF TRANSACTIONS #1*
FROM sarah_tab_1 e,
sarah_tab_3 sa
WHERE e.type = 'CRA'
AND sa.type = 'PRE'
) S1,
(SELECT sa.id, adj.dt AS vdate ** LIST OF TRANSACTIONS #2**
FROM sarah_tab_4 sa,
sarah_tab_5 adj
WHERE sa.id = adj.id
) S2
WHERE S1.id = S2.id
and s2.vdate > S1.DT
** ME TRYING TO COMPARE IF 1 date of TRANS 2 is after 60 days of TRANS 1**
-- and s2.vdate < S1.DT + 60
and s2.vdate not between (S1.DT) and (S1.DT + 60 )
-
Attachment: SQL_help.txt
(Size: 1.93KB, Downloaded 1666 times)
[Updated on: Tue, 20 March 2018 11:44] Report message to a moderator
|
|
|
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668885 is a reply to message #668884] |
Tue, 20 March 2018 11:57 |
|
Saz
Messages: 8 Registered: March 2018
|
Junior Member |
|
|
also just to add the following ID's are in the mock up for each example :
E.g. A - ID : '0518814'
Trans 1 : 1/1/18
Trans 2 : 1/11/17, 1/12/17, 2/1/18, 1/2/18, 1/3/18, 17/3/18
Did a trans 2 occurs within 60 days post trans 1 : Y
--> I don't want to return this example
E.g. B - ID : '0922560'
Trans 1 : 1/1/18
Trans 2 : 1/11/17, 1/12/17, 31/12/17, 17/3/18, 21/3/18
Did a trans 2 occurs within 60 days post trans 1 : N
--> I want to return this example
|
|
|
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668886 is a reply to message #668885] |
Tue, 20 March 2018 12:11 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
It is Worst Practice to use KEYWORD as column name ("TYPE").
create table sarah_tab_1
(
ID Varchar(255),
type varchar(255),
Dt date);
insert into sarah_tab_1 values ( '0518814', 'CRA', '01-JAN-18');
insert into sarah_tab_1 values ( '0922560', 'CRA', '01-JAN-18');
insert into sarah_tab_1 values ( '0057847', 'CRA', '29-NOV-17');
insert into sarah_tab_1 values ( '0050248', 'CRA', '15-DEC-17');
insert into sarah_tab_1 values ( '0498781', 'CRA', '29-NOV-17');
create table sarah_tab_3
(
ID Varchar(255),
type varchar(255));
insert into sarah_tab_3 values ( '0498781', 'PRE');
insert into sarah_tab_3 values ( '0922560', 'PRE');
insert into sarah_tab_3 values ( '0057847', 'PRE');
insert into sarah_tab_3 values ( '0050248', 'PRE');
insert into sarah_tab_3 values ( '0518814', 'PRE');
create table sarah_tab_4
(
ID Varchar(255),
type varchar(255)
);
insert into sarah_tab_4 values ( '0498781', 'PRE');
insert into sarah_tab_4 values ( '0922560', 'PRE');
insert into sarah_tab_4 values ( '0057847', 'PRE');
insert into sarah_tab_4 values ( '0050248', 'PRE');
insert into sarah_tab_4 values ( '0498781', 'PRE');
create table sarah_tab_5
(
ID Varchar(255),
dt varchar(255));
insert into sarah_tab_5 values ( '0518814', '01-NOV-17');
insert into sarah_tab_5 values ( '0518814', '01-DEC-17');
insert into sarah_tab_5 values ( '0518814', '02-JAN-18');
insert into sarah_tab_5 values ( '0518814', '01-FEB-18');
insert into sarah_tab_5 values ( '0518814', '01-MAR-18');
insert into sarah_tab_5 values ( '0518814', '17-MAR-18');
insert into sarah_tab_5 values ( '0922560', '01-NOV-17');
insert into sarah_tab_5 values ( '0922560', '01-DEC-17');
insert into sarah_tab_5 values ( '0922560', '31-DEC-17');
insert into sarah_tab_5 values ( '0922560', '17-MAR-18');
insert into sarah_tab_5 values ( '0922560', '21-MAR-18');
insert into sarah_tab_5 values ( '0057847', '20-MAR-18');
insert into sarah_tab_5 values ( '0050248', '01-dec-17');
insert into sarah_tab_5 values ( '0498781', '01-JUn-18');
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 06:43:30 CDT 2024
|