I am working (in SQL Server 2016) on dental schedules, and by using LEAD (over PARTITION …
I can produce data of this format:
| Chair | Date | StartTime | EndTime | Blocked | Booked | Session |
|---|---|---|---|---|---|---|
| Cent 1 | 2025-03-03 | 490 | 500 | 1 | 0 | |
| Cent 1 | 2025-03-03 | 500 | 510 | 1 | 0 | |
| Cent 1 | 2025-03-03 | 510 | 520 | 0 | 0 | Follow-up |
| Cent 1 | 2025-03-03 | 520 | 530 | 0 | 0 | Follow-up |
| Cent 1 | 2025-03-03 | 530 | 540 | 0 | 0 | Follow-up |
| Cent 1 | 2025-03-03 | 540 | 550 | 0 | 0 | Follow-up |
| Cent 1 | 2025-03-03 | 550 | 560 | 0 | 1 | |
| Cent 1 | 2025-03-03 | 560 | 570 | 0 | 1 | |
| Cent 1 | 2025-03-03 | 570 | 580 | 0 | 1 | |
| Cent 1 | 2025-03-03 | 580 | 590 | 0 | 1 | |
| Cent 1 | 2025-03-03 | 590 | 600 | 0 | 0 | Follow-up |
| Cent 1 | 2025-03-03 | 600 | 610 | 0 | 0 | Assessment |
| Cent 1 | 2025-03-03 | 610 | 620 | 0 | 0 | Assessment |
| Cent 1 | 2025-03-03 | 620 | 630 | 0 | 0 | Assessment |
What I need to achieve (and haven’t been able to find elsewhere in this forum) is for the data to be grouped together to produce:
| Chair | Date | StartTime | EndTime | Label |
|---|---|---|---|---|
| Cent 1 | 2025-03-03 | 490 | 510 | Block |
| Cent 1 | 2025-03-03 | 510 | 550 | Follow-up |
| Cent 1 | 2025-03-03 | 550 | 590 | Booked |
| Cent 1 | 2025-03-03 | 590 | 600 | Follow-up |
| Cent 1 | 2025-03-03 | 600 | 630 | Assessment |
ie, if the values of the last 3 columns don’t change from the row before, they are added into a contiguous block. This example is for the situation where Follow-up was a single session from 510 to 600, but was afterwards overlayed by an appointment being booked.
The time values are minutes-after-midnight; they will be changed at the display stage.
I don’t have experience with CTE, and trying to adapt the answers to other questions to my code isn’t working – what I’ve found is for multi-day blocks being concatenated.
Any assistance much appreciated.
I have achieved the first table above, but can’t find a reference to get to the 2nd.