Hi,
I have a time and attendance table and trying to work out the consecutive streak of absence which will start or end in a status S, SC or SB for each employee. Column in red font below to give example of what the calculated column should look like (alternative is fine too). In order to work out continous periods of absence the calculation will need to take into account two tables of codes. One contains a list of codes that would break a continuous period and another list which does not. Will need to also reset on Employee ID.
T&A table
| Employee ID | Date | Status | Streak | Alternative streak |
| 123 | 02-Feb-22 | RD | | |
| 123 | 03-Feb-22 | 1 | | |
| 123 | 04-Feb-22 | S | 1 | 1 |
| 123 | 05-Feb-22 | RD | | |
| 123 | 06-Feb-22 | RD | | |
| 123 | 07-Feb-22 | 1 | | |
| 123 | 08-Feb-22 | S | | 1 |
| 123 | 09-Feb-22 | SC | | 2 |
| 123 | 10-Feb-22 | SB | 3 | 3 |
123 | 11-Feb-22 | 1 | | |
| 123 | 12-Feb-22 | RD | | |
| 123 | 13-Feb-22 | RD | | |
| 123 | 14-Feb-22 | AL | | |
| 123 | 15-Feb-22 | S | | 1 |
| 123 | 16-Feb-22 | RD | | 2 |
| 123 | 17-Feb-22 | S | | 3 |
| 123 | 18-Feb-22 | S | | 4 |
| 123 | 19-Feb-22 | ME | | 5 |
| 123 | 20-Feb-22 | RD | | 6 |
| 123 | 21-Feb-22 | S | | 7 |
| 123 | 22-Feb-22 | S | 8 | 8 |
| 123 | 23-Feb-22 | RD | | |
| Break codes | | Continuous codes |
| 01 | | AW |
| ET | | BH |
| L | | HA |
| NW | | ME |
| RW | | OD |
| RX | | RD |
| TC | | RB |
| TW | | BL |
| WB | | HR |
| WS | | PL |
| XX | | S |
| ZL | | SB |
| AB | | SC |
| AL | | |
| AS | | |
| CL | | |
| EL | | |
| FL | | |
| HD | | |
| HL | | |
| LC | | |
| MB | | |
| ML | | |
| TA | | |
| UL | | |
