create dataset according to joint/left dates - R

I believe this works. My approach was to reshape the data into longer format, then count each Joint_date line as adding +1 employee, and otherwise we're looking at a departure and -1.

The middle bit converts each date to end of the month, and in the case of a departure to the end of the following month (since you note that we want someone who left in the month to still count in that month; they don't decrement the total until the next month).

The complete(Organisation, ... step adds in blank rows for the months in the period of interest which might have had no change.

Finally, we count how many net additions and departures per month, per organization, with the employee count being the cumulative sum (cumsum) of those changes.

library(tidyverse); library(lubridate)

# convenience function to return the last day of the month
eom <- function(date) { ceiling_date(date, "month") - 1}

dataset1 %>%
  pivot_longer(-c(Employee:Organisation)) %>%
  filter(!is.na(value)) %>%
  mutate(change = if_else(name == "Joint_date", 1, -1),
         date = value %>% ymd %>% eom,
         Month = if_else(change == -1, eom(date + 10), date)) %>%

  complete(Organisation, 
           Month = ceiling_date(seq.Date(ymd(20180101), ymd(20210601), "month"),"month")-1,
           fill = list(change = 0)) %>% 
  count(Organisation, Month, wt = change, name = "change") %>%
  
  group_by(Organisation) %>%
  mutate(Nr_employees = cumsum(change)) %>%
  ungroup() 

I prefer to work with the data.table package - for problems like creating dataset3, the non-equijoin functionality is a great fit.

library(data.table)
setDT(dataset1)    

dataset2 <- CJ(Organisation = dataset1[,unique(Organisation)],
               ## This is an option to generate the month sequence based on the first date in dataset1 to present
               # Month = seq.Date(from = as.Date(cut.Date(dataset1[,min(Joint_date)], breaks = "months")),
               #                  to = as.Date(cut.Date(Sys.Date(), breaks = "months")),
               #                  by = "month") - 1
               ## Otherwise you can still generate a full sequence of month-end dates with just a start and end
               Month = seq.Date(from = as.Date("2018-02-01"),
                                to = as.Date("2021-07-01"),
                                by = "month") - 1)

## Simpler to compare month start dates than end
dataset2[,MonthStart := as.Date(cut.Date(Month, breaks = "months"))]

## Fill NA's for Left_date with today's date to properly account for employees still present
dataset1[,Left_date_fill := data.table::fcoalesce(Left_date, Sys.Date())]

## Create columnns with the month start dates of arrivals/departures
dataset1[,Joint_date_month := as.Date(cut.Date(Joint_date, breaks = "months"))]
dataset1[,Left_date_fill_month := as.Date(cut.Date(Left_date_fill, breaks = "months"))]

## Use a non-equijoin to summarize the number of employees present by month
dataset2[dataset1, Nr_employees := .N, by = .(Organisation,
                                              Month), on = .(Organisation = Organisation,
                                                             MonthStart >= Joint_date_month,
                                                             MonthStart <= Left_date_fill_month)]

## Using this method, the information required for `dataset3` has been added to `dataset2` instead
print(dataset2[seq_len(5), .(Organisation, Month, Nr_employees)])
#    Organisation      Month Nr_employees
# 1:         A123 2018-01-31            2
# 2:         A123 2018-02-28            3
# 3:         A123 2018-03-31            3
# 4:         A123 2018-04-30            3
# 5:         A123 2018-05-31            3
# 6:         A123 2018-06-30            3

To create a summary table like dataset4, it makes the most sense to me to break up each of the steps into a separate operation:

## Start with a table of organizations for dataset4
dataset4 <- data.table(Organisation = dataset1[,unique(Organisation)])

## Join on a summary of dataset2 to get the average over the window of interest
dataset4[dataset2[,.(Avg = mean(fcoalesce(Nr_employees),0.0)), by = .(Organisation)]
         ,Average_Nr_employees := Avg, on = .(Organisation)]

## Join a summary of dataset1 counting the number that joined in the window of interest
dataset4[dataset1[Joint_date_month >= dataset2[,min(MonthStart)]
                  & Joint_date_month <= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_joined := N, on = .(Organisation)]

## Join a summary of dataset1 counting the number that left in the window of interest
dataset4[dataset1[Left_date_fill_month >= dataset2[,min(MonthStart)]
                  & Left_date_fill_month <= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_left := N, on = .(Organisation)]

## Join a summary of dataset1 counting the number that joined before and left after window of interest
dataset4[dataset1[Joint_date_month <= dataset2[,min(MonthStart)]
                  & Left_date_fill_month >= dataset2[,max(MonthStart)]
                  , .(N = .N)
                  , by = .(Organisation)], Nr_employees_stayed := N, on = .(Organisation)]

print(dataset4)
#    Organisation Average_Nr_employees Nr_employees_joined Nr_employees_left Nr_employees_stayed
# 1:         A123             2.738095                   2                 1                   1
# 2:         A333             3.761905                   1                 2                   3
# 3:         A445             2.071429                  NA                 1                   2

Here is an other data.table, but with a different approach than the answer by Matt.

Code explanation is inside the comments

library(data.table)
# Set dataset1 to data.table format
setDT(dataset1)
# Faster way to create dataset 2
dataset2_dates <- seq(as.Date("2018-02-01"), as.Date("2021-07-01"), by = "1 months") - 1
dataset2 <- CJ(Organisation = dataset1$Organisation, 
               Month = dataset2_dates,
               unique = TRUE, sorted = TRUE)
# Create dataset3 using a series of two non-equi joins
dataset2[, Nr_employees := 0]
# First non-equi for people that already left (so month should be between joint-left)
dataset2[dataset1[!is.na(Left_date)], 
         Nr_employees := Nr_employees + .N, 
         by = .(Organisation, Month), 
         on = .(Organisation = Organisation, Month >= Joint_date, Month <= Left_date)]
# Second non-equi for people are still around (so month should be after joint)
dataset2[dataset1[is.na(Left_date)], 
         Nr_employees := Nr_employees + .N, 
         by = .(Organisation, Month), 
         on = .(Organisation = Organisation, Month >= Joint_date)]
#      Organisation      Month Nr_employees
#   1:         A123 2018-01-31            2
#   2:         A123 2018-02-28            3
#   3:         A123 2018-03-31            3
#   4:         A123 2018-04-30            3
#   5:         A123 2018-05-31            3
# ---                                     
# 122:         A445 2021-02-28            2
# 123:         A445 2021-03-31            2
# 124:         A445 2021-04-30            2
# 125:         A445 2021-05-31            2
# 126:         A445 2021-06-30            2

# Initialise dataset4
dataset4 <- dataset2[, .(Average_Nr_employees = mean(Nr_employees)), by = .(Organisation)]
#    Organisation Average_Nr_employees
# 1:         A123             2.714286
# 2:         A333             3.714286
# 3:         A445             2.047619

#set boundaries to summarise on
minDate <- min(dataset2$Month, na.rm = TRUE)
maxDate <- max(dataset2$Month, na.rm = TRUE)

# Now, get relevant rows from dataset1
dataset4[ dataset1[ is.na(Left_date) | Left_date >= minDate, 
                    .(Nr_employees_joined = uniqueN(Employee[Joint_date >= minDate]),
                      Nr_employees_left   = uniqueN(Employee[!is.na(Left_date) & Left_date <= maxDate]),
                      Nr_employees_stayed = uniqueN(Employee[Joint_date <= minDate & (is.na(Left_date) | Left_date >= maxDate)])
                      ), by = .(Organisation)],
          on = .(Organisation)][]
#    Organisation Average_Nr_employees Nr_employees_joined Nr_employees_left Nr_employees_stayed
# 1:         A123             2.714286                   2                 1                   1
# 2:         A333             3.714286                   1                 2                   3
# 3:         A445             2.047619                   0                 1                   2