I have table data like below:

clm1        mem1    01-JAN-18
clm2        mem1    02-JAN-18
clm3        mem1    03-JAN-18
clm3        mem1    04-JAN-18
clm4        mem1    07-JAN-18
clm5        mem1    08-JAN-18
clm5        mem1    09-JAN-18
clm5        mem1    16-JAN-18

My requirement is to get the output as shown below:

mbr_id    count(consecutive_dates)
mem1      4
mem1      3
mem1      1

Count of the dates must be consecutive and must start counting afresh when there is no consecutive date.

From the sample data, we have dates from 01-JAN-18 to 04-JAN-18 continuously. There is a gap after 4-JAN-2018 so it must stop counting after 4-JAN-2018 and start counting afresh from 07-JAN-2018. Since 7th, 8th, and 9th are consecutive, count must come as 3 and then 16th is alone date without consecutive dates so count of 16-JAN-2018 must be 1.


You made me type your data, so this is as much as you get:

create table data
  claim_key varchar2(10 char), 
  mbr_id varchar2(10 char),
  serv_from date

insert into data values ('clm1', 'mem1', date'2018-01-01');
insert into data values ('clm2', 'mem1', date'2018-01-02');
insert into data values ('clm3', 'mem1', date'2018-01-03');
insert into data values ('clm3', 'mem1', date'2018-01-04');
insert into data values ('clm4', 'mem1', date'2018-01-07');
insert into data values ('clm5', 'mem1', date'2018-01-08');
insert into data values ('clm5', 'mem1', date'2018-01-09');
insert into data values ('clm5', 'mem1', date'2018-01-16');

select mbr_id, count(*)  from (
select mbr_id, sum(changed) over (partition by mbr_id order by serv_from) sum_changed from (
select mbr_id, serv_from,
  case when lag(serv_from) over (partition by mbr_id order by serv_from) - serv_from = -1 
    then 0 else 1 end changed 
from data
)) group by mbr_id, sum_changed;

MBR_ID       COUNT(*)
---------- ----------
mem1                4
mem1                3
mem1                1
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.