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)
icon1.gif  Transaction 2 happen within 60 days of Transaction 1 [message #668882] Tue, 20 March 2018 10:33 Go to next message
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 #668883 is a reply to message #668882] Tue, 20 March 2018 10:52 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
>and s2.vdate < S1.E_DT + 60
>and s2.vdate not between (S1.E_DT) and (S1.E_DT + 60 )

How can both be true for the same value of S2.VDATE?

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

We don't have your tables or data so we can't run, test, or improve post SQL.
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668884 is a reply to message #668883] Tue, 20 March 2018 11:41 Go to previous messageGo to next message
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 441 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
BlackSwan
Messages: 26730
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');

Re: Transaction 2 happen within 60 days of Transaction 1 [message #668887 is a reply to message #668886] Tue, 20 March 2018 12:13 Go to previous messageGo to next message
Saz
Messages: 8
Registered: March 2018
Junior Member
It's just a data mock up - I cant share my org data as its sensitive ..
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668888 is a reply to message #668887] Tue, 20 March 2018 13:11 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
What is expected & desired results should be in both words & example using posted sample data?

  1  SELECT e.ID, sa.id  , e.dt 			-- LIST OF TRANSACTIONS #1*
  2	   FROM sarah_tab_1 e,
  3		sarah_tab_3 sa
  4	   WHERE e.type   = 'CRA'
  5*	   AND sa.type	  = 'PRE'
SQL> /

ID		 ID		  DT
---------------- ---------------- ---------
0518814 	 0498781	  01-JAN-18
0518814 	 0922560	  01-JAN-18
0518814 	 0057847	  01-JAN-18
0518814 	 0050248	  01-JAN-18
0518814 	 0518814	  01-JAN-18
0922560 	 0498781	  01-JAN-18
0922560 	 0922560	  01-JAN-18
0922560 	 0057847	  01-JAN-18
0922560 	 0050248	  01-JAN-18
0922560 	 0518814	  01-JAN-18
0057847 	 0498781	  29-NOV-17

ID		 ID		  DT
---------------- ---------------- ---------
0057847 	 0922560	  29-NOV-17
0057847 	 0057847	  29-NOV-17
0057847 	 0050248	  29-NOV-17
0057847 	 0518814	  29-NOV-17
0050248 	 0498781	  15-DEC-17
0050248 	 0922560	  15-DEC-17
0050248 	 0057847	  15-DEC-17
0050248 	 0050248	  15-DEC-17
0050248 	 0518814	  15-DEC-17
0498781 	 0498781	  29-NOV-17
0498781 	 0922560	  29-NOV-17

ID		 ID		  DT
---------------- ---------------- ---------
0498781 	 0057847	  29-NOV-17
0498781 	 0050248	  29-NOV-17
0498781 	 0518814	  29-NOV-17

25 rows selected.

  1  SELECT sa.id, adj.dt AS vdate		-- LIST OF TRANSACTIONS #2**
  2	   FROM sarah_tab_4 sa,
  3	     sarah_tab_5 adj
  4*	   WHERE sa.id = adj.id
SQL> /

ID		 VDATE
---------------- --------------------
0922560 	 01-NOV-17
0922560 	 01-DEC-17
0922560 	 31-DEC-17
0922560 	 17-MAR-18
0922560 	 21-MAR-18
0057847 	 20-MAR-18
0050248 	 01-dec-17
0498781 	 01-JUn-18
0498781 	 01-JUn-18

9 rows selected.


Re: Transaction 2 happen within 60 days of Transaction 1 [message #668889 is a reply to message #668888] Tue, 20 March 2018 13:37 Go to previous messageGo to next message
Saz
Messages: 8
Registered: March 2018
Junior Member
Desired result = ID's :
0922560
0057847
0050248
0498781

The only ID which should not be returned is 0518814

Extended example :
ID 0922560 has a date of 01-Jan-18 in table "sarah_tab_1"

But doesn't have a date within the following 60 days in "sarah_tab_5"
so ID 0922560 is the desired result

Re: Transaction 2 happen within 60 days of Transaction 1 [message #668891 is a reply to message #668889] Tue, 20 March 2018 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
  1  with trans1 as (
  2  SELECT e.ID eid, sa.id  , e.dt edt-- LIST OF TRANSACTIONS #1*
  3    	   FROM sarah_tab_1 e,
  4    		sarah_tab_3 sa
  5    	   WHERE e.type   = 'CRA'
  6    	   AND sa.type	  = 'PRE' )
  7  , trans2 as (
  8   SELECT sa.id, adj.dt AS vdate		-- LIST OF TRANSACTIONS #2**
  9    	   FROM sarah_tab_4 sa,
 10    	     sarah_tab_5 adj
 11    	   WHERE sa.id = adj.id)
 12  SELECT distinct EID, EDT
 13  FROM TRANS1, TRANS2
 14* WHERE EDT BETWEEN VDATE-60 AND VDATE+60
SQL> /

EID		 EDT
---------------- ---------
0498781 	 29-NOV-17
0922560 	 01-JAN-18
0050248 	 15-DEC-17
0518814 	 01-JAN-18
0057847 	 29-NOV-17

Re: Transaction 2 happen within 60 days of Transaction 1 [message #668899 is a reply to message #668891] Wed, 21 March 2018 05:01 Go to previous messageGo to next message
Saz
Messages: 8
Registered: March 2018
Junior Member
Apologies for the delay - Thank you ... I have that working perfectly
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668903 is a reply to message #668899] Wed, 21 March 2018 06:41 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
Saz wrote on Wed, 21 March 2018 03:01
Apologies for the delay - Thank you ... I have that working perfectly
I? You?
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668904 is a reply to message #668903] Wed, 21 March 2018 11:38 Go to previous messageGo to next message
Saz
Messages: 8
Registered: March 2018
Junior Member
What I meant is that I have it translated back to my actual data and it works ... you really are overly quick to jump on the smallest things .. good luck ...
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668905 is a reply to message #668904] Wed, 21 March 2018 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 26730
Registered: January 2009
Location: SoCal
Senior Member
Saz wrote on Wed, 21 March 2018 09:38
What I meant is that I have it translated back to my actual data and it works ... you really are overly quick to jump on the smallest things .. good luck ...
In case you are unaware, small things matter GREATLY when it comes to programming.
Sloppy writing rarely produces working code.
Re: Transaction 2 happen within 60 days of Transaction 1 [message #668906 is a reply to message #668905] Wed, 21 March 2018 11:56 Go to previous message
Saz
Messages: 8
Registered: March 2018
Junior Member
you only pointed it out because you thought I meant "I" got it working - not giving you the credit you wanted - even thought I had already said Thanks....

small things eh !!! Smile
Previous Topic: Comma Seprate
Next Topic: Oracle 10 vs Oracle 12: order from SELECT statement
Goto Forum:
  


Current Time: Fri Aug 14 06:49:32 CDT 2020