I want to calculate agent statuses (ready, break, lunch, training) adjusted for registration.
PostgreSQL version is 11 (possible upgrade to 13).
Table with registrations:
| dt_start | dt_end | agent_id |
|---|---|---|
| 2024-01-01 08:00:00 | 2024-01-01 08:10:00 | 3148 |
| 2024-01-01 08:20:00 | 2024-01-01 08:30:00 | 3148 |
| 2024-01-01 08:31:00 | 2024-01-01 08:33:00 | 3148 |
| 2024-01-01 08:35:00 | 2024-01-01 08:38:00 | 3148 |
| 2024-01-01 08:40:00 | 2024-01-01 08:41:00 | 3148 |
| 2024-01-01 08:44:00 | null | 3148 |
| 2024-01-01 08:25:00 | 2024-01-01 08:35:00 | 31 |
| 2024-01-01 08:40:00 | 2024-01-01 08:45:00 | 31 |
Table with agent_statuses
| dt | agent_id | status |
|---|---|---|
| 2024-01-01 08:00:00 | 3148 | 1 |
| 2024-01-01 08:15:10 | 3148 | 2 |
| 2024-01-01 08:22:30 | 3148 | 3 |
| 2024-01-01 08:34:00 | 3148 | 1 |
| 2024-01-01 08:41:00 | 3148 | 2 |
| 2024-01-01 08:45:00 | 3148 | 3 |
| 2024-01-01 08:20:00 | 31 | 1 |
| 2024-01-01 08:35:00 | 31 | 2 |
I would like to get this result:
*D - for description, not for result.
| *D | dt_start | dt_end | agent_id | status |
|---|---|---|---|---|
| 1 | 2024-01-01 08:00:00 | 2024-01-01 08:10:00 | 3148 | 1 |
| 2 | 2024-01-01 08:20:00 | 2024-01-01 08:22:30 | 3148 | 2 |
| 3 | 2024-01-01 08:22:30 | 2024-01-01 08:30:00 | 3148 | 3 |
| 4 | 2024-01-01 08:31:00 | 2024-01-01 08:33:00 | 3148 | 3 |
| 5 | 2024-01-01 08:35:00 | 2024-01-01 08:38:00 | 3148 | 1 |
| 6 | 2024-01-01 08:40:00 | 2024-01-01 08:41:00 | 3148 | 1 |
| 7 | 2024-01-01 08:44:00 | 2024-01-01 08:45:00 | 3148 | 2 |
| 8 | 2024-01-01 08:45:00 | null | 3148 | 3 |
| 9 | 2024-01-01 08:25:00 | 2024-01-01 08:35:00 | 31 | 1 |
| 10 | 2024-01-01 08:40:00 | 2024-01-01 08:45:00 | 31 | 2 |
Description
- User 3148. Set status 1 at 08:00:00. Registered at 08:00:00. Next status set at 08:15:10, but user have register until 08:10:00. Result start 08:00:00, end 08:10:00.
- User 3148. Set status 2 at 08:15:10, but registered at 08:20:00. Next status set at 08:22:30. User steel registered. Result start 08:20:00, end 08:22:30
- User 3148. Set status 3 at 08:22:30, user registered. Next status set at 08:34:00, but user registered until 08:30:00. Result Start 08:22:30, end 08:30:00.
- User 3148. Status without change. Have one more registration between 08:22:30 and 08:34:00. Register from 08:31:00 to 08:33:00. Result start 08:31:00, end 08:33:00
- User 3148. Set status 1 at 08:34:00, but registration from 08:35:00. Next status set at 08:41:00, but registration until 08:38:00. Result start 08:35:00, end 08:38:00.
- User 3148. Status without change. Have one more registration between 08:34:00 and 08:41:00. Register from 08:40:00 to 08:41:00. Result start 08:40:00, end 08:41:00
- User 3148. Set status 2 at 08:41:00, but registered at 08:44:00. Next status set at 08:45:00. User steel registered. Result start 08:44:00, end 08:45:30
- User 3148. Set status 3 at 08:45:00, steel registered. 3 is current status.
- User 31. Set status 1 at 08:20:00. Registered at 08:25:00. Next status set at 08:35:00, user registered until 08:35:00. Result start 08:25:00, end 08:35:00.
- User 31. Set status 2 at 08:35:00. Registered at 08:40:00. Status no change, but user registered until 08:45:00. Result start 08:40:00, end 08:45:00.
The logic is this:
- If there is a registration, it overlaps the agent status, correct it as is.
- If there was no registration at all during the period, no status record is added.
- If necessary, we adjust the beginning or end of processing of registration data.