Hi.
I'm new with R and I'm looking to a solution for my inquiry.
I have two tables: Schedule and tasks. By the end of the day I want to know how many task was done or are pending by each schedule.
Table schedule
| Schedule | DateBegin | DateEnd |
|---|---|---|
| T1 | 01/03/2020 00:00 | 01/03/2020 06:00 |
| T2 | 01/03/2020 06:00 | 01/03/2020 12:00 |
| T3 | 01/03/2020 12:00 | 01/03/2020 18:00 |
| T4 | 01/03/2020 18:00 | 02/03/2020 00:00 |
| T1 | 02/03/2020 00:00 | 02/03/2020 06:00 |
| T2 | 02/03/2020 06:00 | 02/03/2020 12:00 |
| T3 | 02/03/2020 12:00 | 02/03/2020 18:00 |
| T4 | 02/03/2020 18:00 | 03/03/2020 00:00 |
Table task
| Task | DateTime | Status | Analyst |
|---|---|---|---|
| A1 | 01/03/2020 02:00 | Done | John |
| A2 | 01/03/2020 05:00 | Pending | John |
| A3 | 01/03/2020 10:00 | Done | Ana |
| A4 | 01/03/2020 13:00 | Done | Wendy |
| A1 | 01/03/2020 13:40 | Done | Wendy |
| A2 | 02/03/2020 08:00 | Done | Ana |
| A3 | 02/03/2020 11:00 | Pending | Ana |
| A4 | 02/03/2020 20:00 | Done | Charles |
My expectation
| Schedule | DateBegin | DateEnd | Cont_done | Cont_pending |
|---|---|---|---|---|
| T1 | 01/03/2020 00:00 | 01/03/2020 06:00 | 1 | 1 |
| T2 | 01/03/2020 06:00 | 01/03/2020 12:00 | 1 | 0 |
| T3 | 01/03/2020 12:00 | 01/03/2020 18:00 | 2 | 0 |
| T4 | 01/03/2020 18:00 | 02/03/2020 00:00 | 0 | 0 |
| T1 | 02/03/2020 00:00 | 02/03/2020 06:00 | 0 | 0 |
| T2 | 02/03/2020 06:00 | 02/03/2020 12:00 | 1 | 1 |
| T3 | 02/03/2020 12:00 | 02/03/2020 18:00 | 0 | 0 |
| T4 | 02/03/2020 18:00 | 03/03/2020 00:00 | 1 | 0 |
What would be the best way to resolve it using R?