Hello,
I have the following table with two columns: TestID (for userID) and AppointmentDate of when the user visited the practice.
I need help is to create a flag that will flag if 5 or more visits took place within am 180 day period regardless of when the AppointmentDate (meaning it does not have to be first appointment date it can be appointment date that had 4 more visits after all under 180 days combined).
I have used above function to calculate the difference in days between the preceding and the following date (not sure if that helps).
but I am stuck going forward. Please help.
| TestId | AppointmentDate | Count(AppointmentDate) | AppointmentDate - Above(AppointmentDate) |
| 136a | 2014-12-29 | 1 | - |
| 136a | 2014-12-31 | 1 | 2 |
| 136a | 2015-02-13 | 1 | 44 |
| 136a | 2015-03-03 | 1 | 18 |
| 136a | 2015-03-18 | 1 | 15 |
| 136a | 2015-04-02 | 1 | 15 |
| 136a | 2015-04-24 | 1 | 22 |
| 136a | 2015-05-06 | 1 | 12 |
| 136a | 2015-05-22 | 1 | 16 |
| 136a | 2015-06-18 | 1 | 27 |
| 136a | 2015-09-03 | 1 | 77 |
| 136a | 2015-12-07 | 1 | 95 |
| 136a | 2016-03-09 | 1 | 93 |
| 136a | 2016-06-23 | 1 | 106 |
| 136a | 2016-09-29 | 1 | 98 |
