Count Consecutive Dates

[zcavaricci]

I am in the need of counting Consecutive Days for Patients Blood Glucose Checks. I need to know any instance of consecutive Dates >3 consecutive dates. But if there are more that 1 instance of that, just count it as 1 instance, not each time there is a consecutive days >3.

Table A
Patient_ID	Date_Date
A	1/1/2018
A	1/2/2018
A	1/3/2018
A	1/4/2018
A	1/5/2018
A	1/8/2018
A	1/9/2018
A	1/10/2018
A	1/11/2018
B	1/1/2018
B	1/2/2018
B	1/3/2018
B	1/4/2018
C	1/1/2018
C	1/2/2018
C	1/3/2018
Output
Patient_ID	# Instances Consec Dates
A	1
B	1
C	0

[pbaldy]

The only way that comes to mind would be a custom function. It would accept the patient as an input, open a recordset limited to that patient sorted by date. Loop through tracking each record against the previous and increment a counter when the dates are consecutive, reset it when they're not. Return 1 if the counter gets to 4, 0 if it doesn't.