I am receiving the following error message: Error in mutate():
! Problem while computing CALL_RESPONSE_TIME = bizHrDiff(CALL_DATEADDED, CALL_RESPONSE).
Caused by error in c():
! argument 67 is empty

The error occurs on this code snippet:

call_data_all <- helpdesk_data_raw%>% 
  filter(!is.na(CALL_DATECLOSED), !is.na(CALL_RESPONSE)) %>% 
  mutate(CALL_RESPONSE_TIME = bizHrDiff(CALL_DATEADDED, CALL_RESPONSE),
         CALL_RESOLUTION_TIME = bizHrDiff(CALL_DATEADDED, CALL_DATECLOSED))%>% 

I receive this error message, despite no blank data passing through the CALL_DATEADDED and CALL_RESPONSE objects. NA’s are even excluded via filter in the call_data_all dataframe definition.

The aim of this R script is to perform data processing on our helpdesk call data to calculate business hour differences for response and resolution times, excluding weekends and specified holidays. The script includes several steps: it defines functions to count excluded days (holidays and weekends) and to calculate business hour differences between two timestamps. It then connects to a customer services database to retrieve raw helpdesk data. The script processes the data to create three subsets: calls that are still open, deferred calls that have a response but no closure date, and all other calls. It calculates the business response and resolution times in business hours, adjusts them to days, and adds additional date-related columns for further analysis. The final dataset is prepared by combining the processed data subsets and adding a new column to categorize calls based on the department name.

I did not create this R script originally and the previous developer has since left the business, so I’ll be unable to provide concise context as to why certain syntax has been used.

Please see below for the full R script:

library(cmooreFunctions)
basic_setup()
library(sendmailR)
library(dplyr)
library(lubridate)

#run_my_report <- function() {
excludeDayCount <- Vectorize(function(open, close) {
  
  # Check if the ticket was open and closed on the same day
  if (identical(as.Date(open), as.Date(close))) return (0)
  
  # All the holidays to be excluded need to be put here
  holidays <- as.POSIXct(strptime(c('2020-01-01',
                                    '2020-04-10',
                                    '2020-04-13',
                                    '2020-05-08',
                                    '2020-05-25',
                                    '2020-08-31',
                                    '2020-12-25',
                                    '2020-12-28',
                                    
                                    '2021-01-01',
                                    '2021-04-02',
                                    '2021-04-05',
                                    '2021-05-03',
                                    '2021-05-31',
                                    '2021-08-30',
                                    '2021-12-27',
                                    '2021-12-28',
                                    
                                    '2022-01-03',
                                    '2022-04-15',
                                    '2022-04-18',
                                    '2022-05-02',
                                    '2022-06-02',
                                    '2022-06-03',
                                    '2022-08-29',
                                    '2022-12-26',
                                    '2022-12-27',
                                    
                                    '2023-01-02',
                                    '2023-04-07',
                                    '2023-04-10',
                                    '2023-05-01',
                                    '2023-05-08',
                                    '2023-05-29',
                                    '2023-08-28',
                                    '2023-12-25',
                                    '2023-12-26',
                                    
                                    '2024-01-01',
                                    '2024-03-29',
                                    '2024-04-01',
                                    '2024-05-06',
                                    '2024-05-27',
                                    '2024-08-26',
                                    '2024-12-25',
                                    '2024-12-26',
                                    
                                    ), 
                                  "%Y-%m-%d"))
  
  # Dates between open and close  
  day_seq <- floor_date(seq.Date(as.Date(open + days(1)), as.Date(close), by = "days"), "day")
  
  # Count holidays / weekend days
  return(sum(day_seq %in% holidays | wday(day_seq) %in% c(1,7)))
  
})


bizHrDiff <- function(open, close) {
  
  # Hours from the end of one work day until the start of another
  hours_between_days <- dhours(5.5) + dhours(9)
  
  # Number of days to exclude
  excl_days <- excludeDayCount(open, close)  
  # Number of days in include
  reg_days <- as.integer(as.POSIXct(close) - as.POSIXct(open)) - excl_days 
  
  # Total duration between dates
  span <- as.duration(interval(open, close))
  # Remove the number of holidays and weekends
  span <- span - ddays(excl_days)
  # Remove out of office hours
  span <- span - (reg_days * hours_between_days)
  
  # Return in hours
  return(time_length(span, unit = "hour"))
}


helpdesk_data_raw <- dbGetQuery(conn = dbConnect(odbc(), 'customer_two'), 
                                statement = "SELECT [CALL_CODE]
      ,[CALL_USERNAME]
      ,[CALL_PRIORITYCODE]
      ,[CALL_TITLE]
      ,[CALL_DATEADDED]
      ,[CALL_DATECLOSED]
      ,[CALL_STATUS]
      ,[CALL_OPERATORID]
      ,[CALL_CALLTYPE]
      ,[CALL_USERFAULT]
      ,[CALL_DEPTNAME]
      ,[CALL_USERPHONE]
      ,[CALL_RESPONSE]
  FROM [BLUSH].[dbo].[PHONE];")

call_open <- helpdesk_data_raw %>%
  filter(is.na(CALL_RESPONSE)) %>% 
  mutate(CALL_RESOLUTION_TIME = NA, CALL_RESPONSE_TIME = NA)

call_deferred <- helpdesk_data_raw %>%
  filter(is.na(CALL_DATECLOSED), !is.na(CALL_RESPONSE)) %>% 
  mutate(CALL_RESOLUTION_TIME = NA,
         CALL_RESPONSE_TIME = bizHrDiff(CALL_DATEADDED, CALL_RESPONSE))

call_data_all <- helpdesk_data_raw%>% 
  filter(!is.na(CALL_DATECLOSED), !is.na(CALL_RESPONSE)) %>% 
  mutate(CALL_RESPONSE_TIME = bizHrDiff(CALL_DATEADDED, CALL_RESPONSE),
         CALL_RESOLUTION_TIME = bizHrDiff(CALL_DATEADDED, CALL_DATECLOSED))%>% 
  rbind(call_open, call_deferred) %>% 
  mutate(CALL_DATE = as.Date(CALL_DATEADDED),
         CALL_WEEKDAY = weekdays(CALL_DATE, abbreviate = FALSE),
         CALL_MONTHDAY = mday(CALL_DATE),
         CALL_RESPONSE_TIME = CALL_RESPONSE_TIME / 24,
         CALL_RESOLUTION_TIME = CALL_RESOLUTION_TIME / 24,
         CALL_COMPANY = ifelse(CALL_DEPTNAME == 'OfficeXpress', 'OX', 'UFP'))

• The data type for the CALL_DATEADDED and CALL_RESPONSE objects is POSIXct

  This data will ultimately be uploaded to a SQL table.

Below is some of the sample data, pulled from the [BLUSH].[dbo].[PHONE] table.
Sample Data

I have exported the data for analysis, the data seems fine.

I have tried duplicating the SQL query further down the script, excluding NA’s and blanks, this resulted in the same error.

I updated our R studio and ensured all packages are at current version, nothing.

I’m sure I’ve missed something obvious, as I’ve been looking at this for so long I can’t see the wood for the trees.

Hopefully this is enough context to help, please let me know if anymore is required or if there is a question similar to this that has been asked already – I looked but couldn’t find anything.