Counting between dates

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?

The best way is to use lubridate package to handle the dates.
Perform a left join on your data, then group by and summarise

for more detailed help, I'd request you provide a reprex
FAQ: How to do a minimal reproducible example ( reprex ) for beginners

1 Like

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.