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?