Hi All,
DB 12.1
Im trying to get a count of records where a flag is set for consecutive period up to the one chosen
for example: Given the following data:
I want to run a query that does the following
select count(flag) from the table where the flag is set consecutively. i.e. if my overall query filters on the period 2020-10, the count will come back with 4. if my overall filter is 2020-07 it will come back with 0. If 2020-06 will come back with 2. I've got it working with a messy set of subqueries, but I'm thinking there must be a partition clause which is far more efficient.
For testing: Ive created the following:
create table CONSEC_UNPAID (period varchar2(7), flag varchar2(1));
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-01','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-01','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-02','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-02','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-02','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-03','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-04','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-05','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-06','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-06','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-07','');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-08','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-08','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-09','X');
INSERT INTO CONSEC_UNPAID (period, flag) values ( '2020-10','X');
Id be grateful if someone can help
Rgds
Richard