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.
Too 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.