Stay type based on StartDateTime and EndDateTime

[genghiskhan]

Hi Community
Aim = dplyr 'case when' statement to categorise hospital stay into single, overnight and multiday based on the StartDateTime and EndDateTime
Example = reprex pasted in this query
Desired output column = stay_type in the right end column
Rule = The Length of Stay Type is categoried based on the StartDateTime and EndDateTime and in this if the LOS less than 24 hours and is within the same day, we categorise it as same day.
If LOS less than 24 hours and not same day, then we categorise as overnight and if LOS greater than 24 hours we categorise as multiday.

Caveat = Since using paste as tribble from DATAPASTA, datetime fields are characters
Significance = bed allocation optimisation in a public hospital to ease bed block
Hope you will help me in this.

stay.type <- tibble::tribble(
               ~EncounterNumber, ~Revenue,     ~StartDateTime,       ~EndDateTime, ~LengthOfStay,  ~stay_type,
                           "p1",  1694.64, "30/05/2023 11:12", "31/05/2023 11:10",         0.999, "overnight",
                           "p2",  2768.01, "12/06/2023 19:31", "13/06/2023 19:30",         0.999, "overnight",
                           "p3",  2107.18,   "1/07/2022 9:25",   "2/07/2022 9:25",             1, "overnight",
                           "p4", 10396.07,  "1/09/2022 11:58",  "2/09/2022 11:58",             1, "overnight",
                           "p5", 21203.97, "26/04/2023 10:58", "28/04/2023 11:57",         2.041,  "multiday",
                           "p6", 10655.02,  "2/05/2023 14:47",  "4/05/2023 15:46",         2.041,  "multiday",
                           "p7",  1078.02,   "1/07/2022 8:00",  "1/07/2022 15:41",          0.32,    "single"
               )
stay.type
#> # A tibble: 7 × 6
#>   EncounterNumber Revenue StartDateTime    EndDateTime    LengthOfStay stay_type
#>   <chr>             <dbl> <chr>            <chr>                 <dbl> <chr>    
#> 1 p1                1695. 30/05/2023 11:12 31/05/2023 11…        0.999 overnight
#> 2 p2                2768. 12/06/2023 19:31 13/06/2023 19…        0.999 overnight
#> 3 p3                2107. 1/07/2022 9:25   2/07/2022 9:25        1     overnight
#> 4 p4               10396. 1/09/2022 11:58  2/09/2022 11:…        1     overnight
#> 5 p5               21204. 26/04/2023 10:58 28/04/2023 11…        2.04  multiday 
#> 6 p6               10655. 2/05/2023 14:47  4/05/2023 15:…        2.04  multiday 
#> 7 p7                1078. 1/07/2022 8:00   1/07/2022 15:…        0.32  single

^{Created on 2024-03-20 with reprex v2.1.0}

[FJCC]

I expressed your case_when conditions as
Start and End on the same day ~ single
LengthOfStay <= 1 ~ overnight (Start and End are implied to be on different days by the preceding case)
LengthOfStay > 1 ~ multiday

library(tidyverse)
stay.type <- tibble::tribble(
  ~EncounterNumber, ~Revenue,     ~StartDateTime,       ~EndDateTime, ~LengthOfStay,  ~stay_type,
  "p1",  1694.64, "30/05/2023 11:12", "31/05/2023 11:10",         0.999, "overnight",
  "p2",  2768.01, "12/06/2023 19:31", "13/06/2023 19:30",         0.999, "overnight",
  "p3",  2107.18,   "1/07/2022 9:25",   "2/07/2022 9:25",             1, "overnight",
  "p4", 10396.07,  "1/09/2022 11:58",  "2/09/2022 11:58",             1, "overnight",
  "p5", 21203.97, "26/04/2023 10:58", "28/04/2023 11:57",         2.041,  "multiday",
  "p6", 10655.02,  "2/05/2023 14:47",  "4/05/2023 15:46",         2.041,  "multiday",
  "p7",  1078.02,   "1/07/2022 8:00",  "1/07/2022 15:41",          0.32,    "single"
)

stay.type <- stay.type |> 
  mutate(StartDateTime = dmy_hm(StartDateTime), EndDateTime = dmy_hm(EndDateTime)) |> 
  mutate(stay_type2 = case_when(
    as.Date(StartDateTime) == as.Date(EndDateTime) ~ "single",
    LengthOfStay <= 1 ~ "overnight",
    TRUE ~ "multiday"
))
select(stay.type, stay_type, stay_type2)
#> # A tibble: 7 × 2
#>   stay_type stay_type2
#>   <chr>     <chr>     
#> 1 overnight overnight 
#> 2 overnight overnight 
#> 3 overnight overnight 
#> 4 overnight overnight 
#> 5 multiday  multiday  
#> 6 multiday  multiday  
#> 7 single    single

^{Created on 2024-03-19 with reprex v2.0.2}

[genghiskhan]

@FJCC Thank you so much for your very prompt contribution to improve healthcare here. Appreciated.