Count Duplicate Entries Within Period of 7 Days

Hi Folks,
As per requirement we want to count number of repeat case in the bucket of 7 days. Case appearing in the first 7 days will be counted only 1, case appearing in after 7 days will go into next bucket.

Here in Example A’s Case count is 3 because 7days bucket available are there is 3. First bucket is 1-7 Jan, Next one of 8-14 Jan and last 15-21 Jan.

Image attached for reference.

What will be approach for get this type solution.

Qu

Hi Mukesh,

Please check with the below formula-
COUNT(CASE WHEN DAY(TABLE.COLUMN) IN (1,2,3,4,5,6,7) THEN (TABLE.COLUMN)
WHEN DAY(TABLE.COLUMN) IN (8,9,10,11,12,13,14) THEN (TABLE.COLUMN)
END)

Thanks!!
Pooja

Hi Pooja,

Thanks for the response, but query is to count days after first instance occurrence.

Ex. If A’s first occurrence on 1st Jan then his bucket will be about 1-7 Jan but for B if his first occurrence on 2nd Jan then his bucket will be about 2-8 Jan.