Reading customer data

Question

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.

Answer 1

The following are a few suggestions on how I'd write the SQL as a stored procedure. Then call it from R.

Source Control

If you don't already have a database project, create one in Visual Studio. Then check it in to source control. Microsoft Azure DevOps Services is free & private for teams of 5 or less (this is per project, so 5 developers per project). Then you'll be able to track changes you make to your stored procedures, views, tables, etc.

Formatting

I use the following tools for SSMS and Visual Studio, ApexSQL Refactor and Poor Man's T-Sql Formatter. I use it when I have to edit other developer's code. It's a great way to standardize your SQL. I find it does most of the formatting for me, but I'll still make a few changes after.

Schema Names

Always reference the schema when selecting an object e.g. [dbo].[Sales].

• Also check out the book Clean Code. It will change the way you think about naming conventions.

---

Revised SQL

Without table definitions and sample records I was unable to test this, but it should give you a good start.

CREATE PROCEDURE [dbo].[Patient_Addresses]
(
    @Dsch_Date_Start          AS DATE = '01-JAN-2019'
   , @addr_line1_Rule1        AS VARCHAR(50) = N'101 HOSPITAL RD'
   , @PTNO_NUM_Rule2          AS VARCHAR(1) = N'2'
   , @PTNO_NUM_Rule3          AS VARCHAR(50) = N'1999'
   , @Plm_Pt_Acct_Type_Invoice AS VARCHAR(1) = N'I'
)
AS

BEGIN

   /*
   --use variables to document why the column is being filtered makes it easyier to understand
   DECLARE @Dsch_Date_Start         AS DATE      = '01-JAN-2019'; 
   DECLARE @addr_line1_Rule1            AS VARCHAR(50)  = N'101 HOSPITAL RD';
   DECLARE @PTNO_NUM_Rule2          AS VARCHAR(1)   = N'2';
   DECLARE @PTNO_NUM_Rule3          AS VARCHAR(4)   = N'1999';
   DECLARE @Plm_Pt_Acct_Type_Invoice    AS VARCHAR(1)   = N'I';
   */

   SELECT 
        [PtNo_Num] --missing table/view alias
       , [FullAddress] = [a].[addr_line1] + ', ' + [a].[Pt_Addr_City] + ', ' + [a].[Pt_Addr_State] + ', ' + [a].[Pt_Addr_Zip] --column alias is easyier to read from the left
       , [a].[Pt_Addr_Zip]
       , [PartialAddress] = [a].[Pt_Addr_City] + ', ' + [a].[Pt_Addr_State] + ', ' + [a].[Pt_Addr_Zip]
   FROM
      [smsdss].[c_patient_demos_v] AS [a] --it's not within the best practices to use a prefix or suffix for views
      LEFT OUTER JOIN [smsdss].[BMH_PLM_PtAcct_V] AS [b] --there are a lot of conditions, this may be better done in another view.
         ON [a].[pt_id] = [b].[Pt_No] 
         AND [a].[from_file_ind] = [b].[from_file_ind] 
         AND [a].[Pt_Addr_City] IS NOT NULL --moving criteria to the join may speed up your results
         AND [a].[addr_line1] IS NOT NULL
         AND [a].[Pt_Addr_State] IS NOT NULL
         AND [a].[Pt_Addr_Zip] IS NOT NULL
         AND [a].[addr_line1] != @addr_line1_Rule1
         AND [b].[Plm_Pt_Acct_Type] = @Plm_Pt_Acct_Type_Invoice
         AND [b].[tot_chg_amt] > 0
         AND LEFT([b].[PTNO_NUM], 1) != @PTNO_NUM_Rule2
         AND LEFT([b].[PTNO_NUM], 4) != @PTNO_NUM_Rule3
         AND [b].[Dsch_Date] >= @Dsch_Date_Start
      LEFT OUTER JOIN [smsdss].[c_geocoded_address] AS [c] 
         ON [b].[PtNo_Num] = [c].[Encounter] 
         AND [c].[Encounter] IS NULL
     ;

END 

GO

Possible R script:

library(RODBCext)

dbhandle <- odbcDriverConnect('driver={SQLServer};server=xxx;database=xxx;trusted_connection=true') 

sqlText <- paste("EXEC [dbo].[Patient_Addresses] @Dsch_Date_Start='01-JAN-2019', @addr_line1_Rule1='101 HOSPITAL RD', @PTNO_NUM_Rule2='2', @PTNO_NUM_Rule3='1999', @Plm_Pt_Acct_Type_Invoice='I'")

data <-sqlExecute(channel = dbhandle
, query = sqlText
, data = list(PtNo_Num, FullAddress, Pt_Addr_Zip, PartialAddress)
, fetch = TRUE) 

odbcCloseAll()