You Asked

Team,

Good Monday Morning!!!!!!!!

First let me share the sample table and insert scripts for your reference.

Table creation script :

CREATE TABLE st_lo_trans(customer_name VARCHAR2(10),loan_code NUMBER,loan_tran_code NUMBER,tran_id VARCHAR2(5),orig_tran_code NUMBER);

Sample insert statements :

Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 201, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 202, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 203, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 204, 'NSF', 202);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 206, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 207, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 208, 'NSF', 207);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 209, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 210, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 211, 'NSF', 209);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 301, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 302, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 303, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 304, 'NSF', 303);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 305, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 306, 'NSF', 305);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 307, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 308, 'NSF', 307);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 309, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 310, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 401, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 402, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 403, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 404, 'NSF', 402);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 405, 'NSF', 403);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 406, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 501, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 502, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 503, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 504, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 505, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 506, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 507, 'PAY');
COMMIT;

NOTE : TRAN_ID NSF INDICATES A RETURN FOR THE CORRESPONDING PAYMENT TRANSACTION IN TERMS OF BANKING TERMINOLOGY.(SIMILAR TO CHECK RETURN).

Requirement Description :

let me explain about the table. Its a transaction table and am posting sample data and we can see 4 customers by name John,Tim,Blake and Raymond, and each of the customer is associated with a specific loan number and its unique for each customer,viz John hold 1001 loan code, Tim holds 2001,Blake holds 3001 and Raymond hold 4001.Loan_tran_Code column indicates transaction numbers and they need not be in an order like 101,102,103 as they are generated using a sequence and they may be generated across multiple loan numbers. Tran_id column indicates the Transaction ID , like ADV indicates ADVANCE Transaction,PAY Indicates PAYMENT Transaction, NSF indicates a RETURN transaction for corresponding PAYMENT Transaction and this is tracked with the Orig_Tran_Code column. Orig_tran_Code indicates the loan_tran_Code for which NSF(RETURN) has been posted.

Let's take the example of John with loan_Code : 1001

we can see loan_tran_Code column with value 204 has a tran_id 'NSF' AND it corresponds to the RETURN OF loan_tran_Code 202 and it is specified it orig_tran_code column and 202 is a payment transaction with tran_id PAY.

IN OTHER WORDS LOAN_TRAN_cODE 204 IS A RETURN OR 'NSF' Transaction for loan_tran_Code 202 and it is noted in orig_tran_code column as 202,

Similarly loan_tran_Code 208 is a NSF or Return transaction for loan_tran_Code 207 and 207 is noted in orig_tran_Code column.

we need to fetch the count of consecutive returns. In other words we need to fetch records which HAVE conecutive returns ,like ( PAY,NSF IS ONE RECORD) and (PAY,NSF IS ONE RECORD) SO WE NEED GET CONSECUTIVE RECORDS LIKE PAY,NSF,PAY,NSF

FOR TIM WE CAN SEE THREE CONSECUTIVE RETURNS, LIKE LOAN_TRAN_CODE 304 IS RETURN FOR LOAN_TRAN_cODE 303, 306 IS RETURN FOR 305 AND 308 IS RETURN FOR 307 AND WE CAN SEE THEY ARE OCCURRING CONSECUTIVELY,

AND FOR BLAKE WE ARE SEEING LOAN_TRAN_cODE 404 is RETURN FOR LOAN_TRAN_cODE 402 AND 405 IS RETURN FOR LOAN_TRAN_cODE 403.(WE ARE SEEING CONSECUTIVE NSF or RETURNS FOR BLAKE WITH LOAN_TRAN_CODES 404 AND 405 BEING THE RETURNS FOR 402 AND 403 CONSECTIVELY. HERE WE ARE SEEING 2 RETURNS CONSECUTIVELY WITH LOAN_TRAN_CODE 404 AND 405 FOR 2 CONSECUTIVE PAYMENTS WITH LOAN_TRAN_cODES 402 AND 403 )

SO WE NEED TO GET THE consecutive RETURN COUNT FOR TIM AS 3 AND FOR BLAKE WE NEED TO GET THE consecutive COUNT OF RETURNS AS 2 AND FOR John and Raymond the consecutive RETURN count is 0.

Regards,
Vinesh.

and Chris said...

I've made the assumption that you can only have PAY and NSF transactions in the chain. i.e. if you have PAY,ADV,PAY, the two PAY transactions aren't consecutive. Also that NSF transactions refunded in the order the PAY transactions are made.

I've also added another separate group of 3 consecutive PAY transactions for Blake to ensure you find these:

CREATE TABLE st_lo_trans(
  customer_name VARCHAR2(10),loan_code NUMBER,loan_tran_code NUMBER,
  tran_id VARCHAR2(5),orig_tran_code NUMBER
);

Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 201, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 202, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 203, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 204, 'NSF', 202);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 206, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 207, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 208, 'NSF', 207);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 209, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('John', 1001, 210, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('John', 1001, 211, 'NSF', 209);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 301, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 302, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 303, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 304, 'NSF', 303);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 305, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 306, 'NSF', 305);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 307, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('Tim', 2001, 308, 'NSF', 307);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 309, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('Tim', 2001, 310, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 401, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 402, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 403, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 404, 'NSF', 402);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 405, 'NSF', 403);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 406, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 501, 'ADV');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 502, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 503, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 504, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 505, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 506, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('RAYMOND', 4001, 507, 'PAY');

Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 407, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 408, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID)
Values
('BLAKE', 3001, 409, 'PAY');
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 410, 'NSF', 407);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 411, 'NSF', 408);
Insert into ST_LO_TRANS
(CUSTOMER_NAME, LOAN_CODE, LOAN_TRAN_CODE, TRAN_ID, ORIG_TRAN_CODE)
Values
('BLAKE', 3001, 412, 'NSF', 409);

commit;

With these assumptions, the values for the loan_tran_code and orig_tran_code in a group are themselves consecutive. So you just need to group these together!

You can do this by:

- Selecting only the NSF transactions
- Unpivoting the loan_tran_code and orig_tran_code from this

select *
from   st_lo_trans t
unpivot (
  tcode for 
  src_col in (orig_tran_code, loan_tran_code)
) 
where  tran_id = 'NSF'
order  by customer_name, tcode;

CUSTOMER_NAME  LOAN_CODE  TRAN_ID  SRC_COL         TCODE  
BLAKE          3,001      NSF      ORIG_TRAN_CODE  402    
BLAKE          3,001      NSF      ORIG_TRAN_CODE  403    
BLAKE          3,001      NSF      LOAN_TRAN_CODE  404    
BLAKE          3,001      NSF      LOAN_TRAN_CODE  405    
BLAKE          3,001      NSF      ORIG_TRAN_CODE  407    
BLAKE          3,001      NSF      ORIG_TRAN_CODE  408    
BLAKE          3,001      NSF      ORIG_TRAN_CODE  409    
BLAKE          3,001      NSF      LOAN_TRAN_CODE  410    
BLAKE          3,001      NSF      LOAN_TRAN_CODE  411    
BLAKE          3,001      NSF      LOAN_TRAN_CODE  412    
John           1,001      NSF      ORIG_TRAN_CODE  202    
John           1,001      NSF      LOAN_TRAN_CODE  204    
John           1,001      NSF      ORIG_TRAN_CODE  207    
John           1,001      NSF      LOAN_TRAN_CODE  208    
John           1,001      NSF      ORIG_TRAN_CODE  209    
John           1,001      NSF      LOAN_TRAN_CODE  211    
Tim            2,001      NSF      ORIG_TRAN_CODE  303    
Tim            2,001      NSF      LOAN_TRAN_CODE  304    
Tim            2,001      NSF      ORIG_TRAN_CODE  305    
Tim            2,001      NSF      LOAN_TRAN_CODE  306    
Tim            2,001      NSF      ORIG_TRAN_CODE  307    
Tim            2,001      NSF      LOAN_TRAN_CODE  308

This gives you all the tran_code values in a single column. To group consecutive values, use the Tabibitosan method:

with rws as (
  select *
  from   st_lo_trans t
  unpivot (
    tcode for 
    src_col in (orig_tran_code, loan_tran_code)
  ) 
  where  tran_id = 'NSF'
), grps as (
  select r.*, 
         tcode - row_number() over (partition by customer_name order by tcode) grp
  from   rws r
)
  select customer_name, floor(count(*)/2)
  from   grps g
  group  by customer_name, grp
  having count(*) >= 4;

CUSTOMER_NAME  FLOOR(COUNT(*)/2)  
Tim            3                  
BLAKE          3                  
BLAKE          2

You need to divide the count at the end by two because there's two rows/return. The having clause excludes groups with just one return.

For more about Tabibitosan, watch Connor's video on grouping ranges:

https://www.youtube.com/watch?v=yvimYixXo2Q

And more about unpivot, read:

https://blogs.oracle.com/sql/entry/how_to_convert_rows_to#unpivot

PS - Please: DON'T SHOUT!

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Vinesh, February 13, 2017 - 1:50 pm UTC

THanks for the response.

I could not understand something related to this quote in your reply:

PS - Please: DON'T SHOUT!

i did not understand why it has come up in the response.

Please let me know if it's because of upper case being used in my text and if that's the case then, sincere apologies from my end.

Those came from my end as i was entering upper case letter for the contents of Tran_id column and by mistake other words also have come up in upper case.

February 13, 2017 - 3:23 pm UTC

Typing in ALL CAPS is often considered shouting! Thanks for apologizing :)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

Analytics

How to get the count of consecutive transactions

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

Analytics