5 SQL tag cleanup: Improved formatting and language; edited tags, title
source | link

how to find consecutive Consecutive dates count?

I have table datalikedata like below:

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

myMy requirement is togetto get the output as shown below. count:

mbr_id    count(consecutive_dates)
mem1      4
mem1      3
mem1      1

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

From the above examplesample data, we have dates from 01-JAN-18 to 04-JAN-18 continuously and there. There is a gap after 4-janJAN-2018 so it must stop counting after 4-janJAN-2018. and it must start counting freshlyafresh from 7th jan07-JAN-2018  .since Since 7th,8th & 8th, and 9th are consecutive, count must come as 3 and then 16th is alone date with outwithout consecutive dates so count of 16-janJAN-2018 must be 1.

mbr_id    count(consecutive_dates)
mem1           4
mem1           3
mem1           1

how to find consecutive dates count?

I have table datalike 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 toget the output as shown below. count of the dates must be consecutive and sql must start counting freshly when there is no consecutive date. from the above example we have dates from 01-JAN-18 to 04-JAN-18 continuously and there is gap after 4-jan-2018 so it must stop counting after 4-jan-2018. and it must start counting freshly from 7th jan-2018  .since 7th,8th & 9th are consecutive, count must come as 3 and then 16th is alone date with out consecutive dates so count of 16-jan-2018 must be 1.

mbr_id    count(consecutive_dates)
mem1           4
mem1           3
mem1           1

Consecutive dates count

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.

4 edited tags
| link
3 added 498 characters in body; edited tags
source | link

I have table likedatalike 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

I must getmy requirement is toget the output likeas shown below. count of the dates must be consecutive and sql must start counting freshly when there is no consecutive date. from the above example we have dates from 01-JAN-18 to 04-JAN-18 continuously and there is gap after 4-jan-2018 so it must stop counting after 4-jan-2018. and it must start counting freshly from 7th jan-2018 .since 7th,8th & 9th are consecutive, count must come as 3 and then 16th is alone date with out consecutive dates so count of 16-jan-2018 must be 1.

mbr_id    count(consecutive_dates)
mem1           4
mem1           2
mem1           13
mem1           1

I have table 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

I must get output like

mbr_id    count(consecutive_dates)
mem1           4
mem1           2
mem1           1
mem1           1

I have table datalike 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 toget the output as shown below. count of the dates must be consecutive and sql must start counting freshly when there is no consecutive date. from the above example we have dates from 01-JAN-18 to 04-JAN-18 continuously and there is gap after 4-jan-2018 so it must stop counting after 4-jan-2018. and it must start counting freshly from 7th jan-2018 .since 7th,8th & 9th are consecutive, count must come as 3 and then 16th is alone date with out consecutive dates so count of 16-jan-2018 must be 1.

mbr_id    count(consecutive_dates)
mem1           4
mem1           3
mem1           1
2 Formatting the code
source | link
1
source | link