Consecutive dates count

Question

I have table data like below:

CLIAM_KEY   MBR_ID  SERV_FROM
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.

Answer 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');
commit;

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