2
\$\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.

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.

\$\endgroup\$
2
  • \$\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
2
\$\begingroup\$

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() 
\$\endgroup\$

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.