Greetings,
I have the below sample data set (CREATE TABLE and INSERT statements available below)
Would like to classify the records using the following criteria:
Type 1 - this is where all the records associated with the account_id have a fee_trans_id populated - this is very easy to do I guess just by doing a count of record for account_id VS count of non NULLs for fee_trans_id, if equal then it's Type 1
Type 2 - this is where there is one or more NULL fee_trans_id in the middle of the data set - there has to be one gap though as below - this I don't know how to do!
Type 3 - this is where there is one or more NULLs for fee_trans_id but they are at the end of the data set (note the data set is ordered y the fee_date descending order)
Type 4 - This is where there is one or more NULLs that exist at the top of the data set only
Type 5 - This is where there are multiple NULL islands in the data set
What I would like is to create an output table with Account_ID and Type
so it would be something like
Account_ID Data set type
1111 Type 2
2222 Type 3
3333 Type 1
4444 Type 4
5555 Type 5
How do I go about doing this please?
Sample data as below:
CREATE TABLE wkcm_fee_dates (
account_id NUMBER(10),
fee_id NUMBER(10),
fee_trans_id NUMBER(10),
fee_date DATE);
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 135120312, 635140982, to_date('28/02/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 136354766, 645340956, to_date('31/03/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 135523977, NULL, to_date('30/04/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 135120316, NULL, to_date('31/05/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 135120320, NULL, to_date('30/06/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 137371858, 849226956, to_date('31/07/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(1111, 138256173, 617604810, to_date('31/08/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(2222, 103878650, 209160021, to_date('31/01/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(2222, 149715741, 209170722, to_date('28/02/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(2222, 104073401, 226079327, to_date('31/03/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(2222, 100337108, NULL, to_date('30/04/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(3333, 100211114, 226105005, to_date('31/01/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(3333, 100707581, 209132346, to_date('28/02/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(3333, 103856656, 226105418, to_date('31/03/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(3333, 101182304, 226120759, to_date('31/10/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(4444, 100300275, NULL, to_date('31/07/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(4444, 100209868, NULL, to_date('31/08/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(4444, 101178391, 226226593, to_date('30/09/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(4444, 101148011, 226121546, to_date('31/10/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(4444, 100614147, 226228613, to_date('30/11/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 101996923, 226383180, to_date('31/01/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 101796825, 226384616, to_date('28/02/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 103855158, NULL, to_date('31/03/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 100434368, NULL, to_date('30/04/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 100672812, 226382029, to_date('31/05/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 104067576, 225841382, to_date('30/06/2019', 'DD/MM/YYYY'));
INSERT INTO wkcm_fee_dates (account_id, fee_id, fee_trans_id, fee_date) VALUES(5555, 104025986, NULL, to_date('31/07/2019', 'DD/MM/YYYY'));