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.