1
\$\begingroup\$

This is the first time I am writing R code to access/manipulate and save data. The SQL query I would normally run then save to a spreadsheet, read it into R, save to another sheet then copy and paste that yet to another spreadsheet, then import back into SQL-Server.

To much going on so I wanted to put it all together into one script. The SQL Script is fairly straightforward:

SELECT PtNo_Num
, a.addr_line1 + ', ' + a.Pt_Addr_City + ', ' + a.Pt_Addr_State + ', ' + a.Pt_Addr_Zip AS [FullAddress] 
, a.Pt_Addr_Zip
, a.Pt_Addr_City + ', ' + a.Pt_Addr_State + ', ' + a.Pt_Addr_Zip AS [PartialAddress]

FROM smsdss.c_patient_demos_v AS A
LEFT OUTER JOIN smsdss.BMH_PLM_PtAcct_V AS B
ON A.pt_id = B.Pt_No
    AND A.from_file_ind = B.from_file_ind
LEFT OUTER JOIN SMSDSS.c_geocoded_address AS C
ON B.PtNo_Num = C.Encounter

WHERE a.Pt_Addr_City IS NOT NULL 
AND a.addr_line1 IS NOT NULL 
AND a.Pt_Addr_State IS NOT NULL 
AND a.Pt_Addr_Zip IS NOT NULL 
AND b.Plm_Pt_Acct_Type = 'I' 
AND b.tot_chg_amt > 0 
AND LEFT(B.PTNO_NUM, 1) != '2' 
AND LEFT(B.PTNO_NUM, 4) != '1999'
AND B.Dsch_Date >= '2019-01-01'
AND A.addr_line1 != '101 HOSPITAL RD'
AND C.Encounter IS NULL

The R script so far is as below:

# Lib Load ----
if(!require(pacman)) install.packages("pacman")

pacman::p_load(
    # DB Packages
    "DBI",
    "odbc",

    # Tidy
    "tidyverse",
    "dbplyr",
    "writexl",

    # Mapping Tools
    "tmaptools"
)

# Connection Obj ----
con <- dbConnect(
    odbc(),
    Driver = "SQL Server",
    Server = "BMH-HIDB",
    Database = "SMSPHDSSS0X0",
    Trusted_Connection = "TRUE"
)

# Tables ----
pav <- dplyr::tbl(
    con
    , in_schema(
        "smsdss"
        , "BMH_Plm_PtAcct_V"
        )
    ) %>%
    filter(
        tot_chg_amt > 0
        , Dsch_Date >= '2019-01-01'
        , Plm_Pt_Acct_Type == "I"
    ) %>%
    select(
        Plm_Pt_Acct_Type
        , tot_chg_amt
        , PtNo_Num
        , Pt_No
        , Dsch_Date
        , from_file_ind
    )

pdv <- tbl(
    con
    , in_schema(
        "smsdss"
        , "c_patient_demos_v"
    )
)

# Query ----
geo_add <- tbl(
    con
    , in_schema(
        "smsdss"
        , "c_geocoded_address"
    )
)

a <- pdv %>%
    left_join(
        pav
        , by = c(
            "pt_id"="Pt_No"
            , "from_file_ind" = "from_file_ind"
            )
        , keep = T
    ) 

add_geo <- a %>%
    left_join(
        geo_add
        , by = c(
            "PtNo_Num" = "Encounter"
        )
        , keep = T
    ) %>% 
    select(
        PtNo_Num
        , addr_line1
        , Pt_Addr_City
        , Pt_Addr_State
        , Pt_Addr_Zip
        , ZipCode
        , Plm_Pt_Acct_Type
        , tot_chg_amt
        , Dsch_Date
    ) %>%
    filter(
        !is.na(Pt_Addr_City)
        , !is.na(addr_line1)
        , !is.na(Pt_Addr_State)
        , !is.na(Pt_Addr_Zip)
        , !is.na(Plm_Pt_Acct_Type)
        , !is.na(tot_chg_amt)
        , !is.na(Dsch_Date)
        , addr_line1 != '101 Hospital Rd'
        , is.na(ZipCode)
    )

# Make df ----
df <- tibble() # Necessary?
df <- add_geo %>% 
    as_tibble() %>%
    filter(str_sub(PtNo_Num, 1, 1) != 2)

origAddress <-  df %>%
    mutate(
        FullAddress = str_c(
            addr_line1
            , Pt_Addr_City
            , Pt_Addr_State
            , Pt_Addr_Zip
            , sep = ', '
        )
        , PartialAddress = str_c(
            Pt_Addr_City
            , Pt_Addr_State
            , Pt_Addr_Zip
            , sep = ', '
        )
    ) %>%
    select(
        PtNo_Num
        , FullAddress
        , Pt_Addr_Zip
        , PartialAddress
    ) %>%
    rename(
        Encounter = PtNo_Num
        , ZipCode = Pt_Addr_Zip
    )

It seems quite long compared to the SQL, is there a way to write this better or is it what it is so to speak.

\$\endgroup\$
  • \$\begingroup\$ those table & column names are pretty bad \$\endgroup\$ – dustytrash Dec 9 '19 at 17:58
  • 1
    \$\begingroup\$ @dustytrash please don't review the code in the comments. Instead write an answer . Thanks! \$\endgroup\$ – Vogel612 Dec 9 '19 at 18:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.