SQLpulls.Rmd

---
title: "SQL pulls for SMT Dental Pack"
---
```{r}
library(tidyverse)
library(readxl)
library(DBI)
library(odbc)
library(downloadthis)
library(lubridate)
```

```{r working days}

#read local file -- No working days in each month
working_days <- read_excel("workdays.xlsx",sheet = "workdays")
working_days$month <- as.Date(working_days$Month)

```

```{r contractors}

#extract contractual_dataset from NCDR
pull_contractual_dataset <- function(){
  
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  sql <- "SELECT [YEAR_MONTH],
  [CONTRACT_NUMBER]
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_Contracts]"
  result <- dbSendQuery(con, sql)
  contractual_dataset <- dbFetch(result)
  dbClearResult(result)
  
  contractual_dataset
}

#remove duplicates
contractual_dataset <- pull_contractual_dataset() %>%
  mutate(YEAR_MONTH = as.Date(YEAR_MONTH))%>% 
  group_by(YEAR_MONTH) %>% 
  summarize(CONTRACT_NUMBER = unique(CONTRACT_NUMBER))

```

```{r UDA}

#extract UDA calendar data excluding FDs
pull_UDA_calendar_data <- function(){
  
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  sql <- "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_UDA_Activity] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery(con, sql)
  UDA_calendar_data <- dbFetch(result)
  dbClearResult(result)
  
  UDA_calendar_data
}

#only include contractors in contractual_dataset
UDA_calendar_data <- pull_UDA_calendar_data() %>%
  inner_join(contractual_dataset,by=c('YEAR_MONTH','CONTRACT_NUMBER'))

#exclude those with UDA target less than 100
UDA_calendar_data <- dplyr::filter(UDA_calendar_data, as.numeric(UDA_PERF_TARGET) > 100)
names(UDA_calendar_data) <- base::tolower(names(UDA_calendar_data))

# calculate total FP17s and rename fields
UDA_calendar_data<-UDA_calendar_data %>%
  mutate(general_FP17s=rowSums(
    select(., band_1_delivered, band_2_delivered, band_3_delivered, band_urgent_delivered, band_other_delivered), 
    na.rm = TRUE)) %>%
  rename(month = year_month, 
         annual_contracted_UDA = uda_perf_target, 
         UDA_delivered = uda_delivered,
         commissioner_ods_code_icb = commissioner_code, 
         name_or_company_name = provider_name,
         FP17s_band_1 = band_1_delivered, 
         FP17s_band_2 = band_2_delivered, 
         FP17s_band_2a = band_2a_delivered,
         FP17s_band_2b = band_2b_delivered, 
         FP17s_band_2c = band_2c_delivered, 
         FP17s_band_3 = band_3_delivered,
         FP17s_band_urgent = band_urgent_delivered, 
         FP17s_band_other = band_other_delivered,
         UDA_band_1 = uda_band_1_delivered, 
         UDA_band_2 = uda_band_2_delivered, 
         UDA_band_3 = uda_band_3_delivered,
         UDA_urgent = uda_band_urgent_delivered, 
         UDA_other = uda_band_other_delivered)

# reformat region and commissioner names
UDA_calendar_data$region_name <- str_to_title(UDA_calendar_data$region_name)
UDA_calendar_data$commissioner_name <- str_to_title(UDA_calendar_data$commissioner_name)
UDA_calendar_data$commissioner_name <- substr(UDA_calendar_data$commissioner_name,1,
                                              nchar(UDA_calendar_data$commissioner_name)-3)
UDA_calendar_data$commissioner_name <- paste0(UDA_calendar_data$commissioner_name, 
                                              rep("ICB", nrow(UDA_calendar_data)))

```



```{r UDA WITH FD}

#extract UDA calendar data for Foundation Dentists (FD) only
pull_UDA_calendar_data_FD_only <- function(){
  
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  sql <- "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_UDA_Activity_FD_only] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery(con, sql)
  pull_UDA_calendar_data_FD_only <- dbFetch(result)
  dbClearResult(result)
  
  pull_UDA_calendar_data_FD_only
}

#only include contractors in contractual_dataset

UDA_calendar_data_FD_only <- pull_UDA_calendar_data_FD_only()%>%
  inner_join(contractual_dataset,by=c('YEAR_MONTH','CONTRACT_NUMBER'))
#exclude those with UDA target less than 100 - rename fields
UDA_calendar_data_FD_only = dplyr::filter(UDA_calendar_data_FD_only, as.numeric(UDA_PERF_TARGET) > 100)
names(UDA_calendar_data_FD_only) <- base::tolower(names(UDA_calendar_data_FD_only))
UDA_calendar_data_FD_only<-UDA_calendar_data_FD_only%>%
  rename(month=year_month, annual_contracted_UDA=uda_perf_target, UDA_delivered=uda_delivered,
         commissioner_ods_code_icb=commissioner_code, name_or_company_name=provider_name,
         FP17s_band_1=band_1_delivered, FP17s_band_2=band_2_delivered, FP17s_band_2a=band_2a_delivered,
         FP17s_band_2b=band_2b_delivered, FP17s_band_2c=band_2c_delivered, FP17s_band_3=band_3_delivered,
         FP17s_band_urgent=band_urgent_delivered, FP17s_band_other=band_other_delivered,
         UDA_band_1=uda_band_1_delivered, UDA_band_2=uda_band_2_delivered, UDA_band_3=uda_band_3_delivered,
         UDA_urgent=uda_band_urgent_delivered, UDA_other=uda_band_other_delivered)

# reformat region and commissioner names
UDA_calendar_data_FD_only$region_name<- str_to_title(UDA_calendar_data_FD_only$region_name)
UDA_calendar_data_FD_only$commissioner_name<- str_to_title(UDA_calendar_data_FD_only$commissioner_name)
UDA_calendar_data_FD_only$commissioner_name <-   
  substr(UDA_calendar_data_FD_only$commissioner_name,1,nchar(UDA_calendar_data_FD_only$commissioner_name)-3)
UDA_calendar_data_FD_only$commissioner_name<- paste0(UDA_calendar_data_FD_only$commissioner_name, 
                                                     rep("ICB", nrow(UDA_calendar_data_FD_only)))

#calculate  total no FP17s 
UDA_calendar_data_FD_only<-UDA_calendar_data_FD_only %>%
  mutate(general_FP17s=rowSums(
    select(., FP17s_band_1, FP17s_band_2, FP17s_band_3, FP17s_band_urgent, FP17s_band_other), 
    na.rm = TRUE))%>%
  select("month","commissioner_ods_code_icb" ,"commissioner_name", "region_name","contract_number","child_12m_count" ,
         "adult_24m_count","general_FP17s","FP17s_band_1","FP17s_band_2","FP17s_band_2a","FP17s_band_2b","FP17s_band_2c",
         "FP17s_band_3","FP17s_band_urgent","FP17s_band_other","UDA_delivered","UDA_band_1","UDA_band_2",
         "uda_band_2a_delivered","uda_band_2b_delivered","uda_band_2c_delivered","UDA_band_3","UDA_urgent","UDA_other")


#calculate UDA_calendar_data with FD = UDA_calendar_data + UDA_calendar_data_FD_only
UDA_calendar_data_FD <- UDA_calendar_data %>%
  left_join(UDA_calendar_data_FD_only, by=c("month","commissioner_ods_code_icb","commissioner_name","contract_number" )) %>%
  mutate(UDA_delivered = rowSums(select(., UDA_delivered.x, UDA_delivered.y), na.rm = TRUE),
    FP17s_general = rowSums(select(., general_FP17s.x, general_FP17s.y), na.rm = TRUE),
    FP17s_band_1 = rowSums(select(., FP17s_band_1.x, FP17s_band_1.y), na.rm = TRUE),
    FP17s_band_2 = rowSums(select(., FP17s_band_2.x, FP17s_band_2.y), na.rm = TRUE),
    FP17s_band_3 = rowSums(select(., FP17s_band_3.x, FP17s_band_3.y), na.rm = TRUE),
    FP17s_band_urgent = rowSums(select(., FP17s_band_urgent.x, FP17s_band_urgent.y), na.rm = TRUE),
    FP17s_band_other = rowSums(select(., FP17s_band_other.x, FP17s_band_other.y), na.rm = TRUE),
    child_12m_count = rowSums(select(., child_12m_count.x, child_12m_count.y), na.rm = TRUE),
    adult_24m_count = rowSums(select(., adult_24m_count.x, adult_24m_count.y), na.rm = TRUE),
    FP17s_band_2a = rowSums(select(., FP17s_band_2a.x, FP17s_band_2a.y), na.rm = TRUE),
    FP17s_band_2b = rowSums(select(., FP17s_band_2b.x, FP17s_band_2b.y), na.rm = TRUE),
    FP17s_band_2c = rowSums(select(., FP17s_band_2c.x, FP17s_band_2c.y), na.rm = TRUE),
    UDA_band_1 = rowSums(select(., UDA_band_1.x, UDA_band_1.y), na.rm = TRUE),
    UDA_band_2 = rowSums(select(., UDA_band_2.x, UDA_band_2.y), na.rm = TRUE),
    uda_band_2a_delivered = rowSums(select(., uda_band_2a_delivered.x, uda_band_2a_delivered.y), na.rm = TRUE),
    uda_band_2b_delivered = rowSums(select(., uda_band_2b_delivered.x, uda_band_2b_delivered.y), na.rm = TRUE),
    uda_band_2c_delivered = rowSums(select(., uda_band_2c_delivered.x, uda_band_2c_delivered.y), na.rm = TRUE),
    UDA_band_3 = rowSums(select(., UDA_band_3.x, UDA_band_3.y), na.rm = TRUE),
    UDA_urgent = rowSums(select(., UDA_urgent.x, UDA_urgent.y), na.rm = TRUE),
    UDA_other = rowSums(select(., UDA_other.x, UDA_other.y), na.rm = TRUE)) %>% 
  select(final_yn, month, commissioner_ods_code_icb, commissioner_name, region_name = region_name.x, contract_number,
         name_or_company_name, contract_type, ppc_address_postcode, lsoa11_code, annual_contracted_UDA, uda_fin_val, 
         general_dental_fin_value, UDA_delivered, FP17s_general, FP17s_band_1, FP17s_band_2, FP17s_band_3, FP17s_band_urgent, 
         FP17s_band_other, child_12m_count, adult_24m_count, FP17s_band_2a, FP17s_band_2b, FP17s_band_2c, 
         UDA_band_1, UDA_band_2, uda_band_2a_delivered, uda_band_2b_delivered, uda_band_2c_delivered, UDA_band_3, 
         UDA_urgent, UDA_other)

```


```{r UOA}
#extract UDA calendar data excluding FDs
pull_UOA_calendar_data <- function(){

  con <- dbConnect(odbc::odbc(), "NCDR")

  sql <- "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_UOA_Activity] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery(con, sql)
  UOA_calendar_data <- dbFetch(result)
  dbClearResult(result)

  UOA_calendar_data
}

#only include contractors in contractual_dataset
UOA_calendar_data<-pull_UOA_calendar_data() %>%
  inner_join(contractual_dataset,by=c('YEAR_MONTH','CONTRACT_NUMBER'))

#exclude those with UDA target less than 100 and rename fields
UOA_calendar_data <- dplyr::filter(UOA_calendar_data, as.numeric(UOA_PERF_TAR) > 0)
names(UOA_calendar_data) <- base::tolower(names(UOA_calendar_data))

UOA_calendar_data <- UOA_calendar_data %>%
  rename(month = year_month, 
         annual_contracted_UOA = uoa_perf_tar, 
         UOA_delivered = uoa_delivered,
         commissioner_ods_code_icb = commissioner_code, 
         name_or_company_name = provider_name)

UOA_calendar_data$region_name<- str_to_title(UOA_calendar_data$region_name)
UOA_calendar_data$commissioner_name<- str_to_title(UOA_calendar_data$commissioner_name)
UOA_calendar_data$commissioner_name = substr(UOA_calendar_data$commissioner_name,1,nchar(UOA_calendar_data$commissioner_name)-3)
UOA_calendar_data$commissioner_name<- paste0(UOA_calendar_data$commissioner_name, rep("ICB", nrow(UOA_calendar_data)))

```


```{r unique patients}
# extract unique patients seen
pull_unique_patients <- function(){
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  sql <- "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_Unique_rolling] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery(con, sql)
  unique_patients_rolling <- dbFetch(result)
  dbClearResult(result)
  
  unique_patients_rolling

#rename variables  
  names(unique_patients_rolling) <- base::tolower(names(unique_patients_rolling))
  
  unique_patients_rolling <- unique_patients_rolling %>% 
    rename(month = year_month,
           commissioner_ods_code_icb = commissioner_code)
}
```

```{r npp activity}
# extract npp data
pull_npp_data <- function(){
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  npp_data <- dbGetQuery(con, "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_NPP_Eligible_Activity] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  ,[STP_Name]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b
  ON a.[COMMISSIONER_CODE] = b.[STP_Code]")
}

# filter and calculate totals
npp_data <- pull_npp_data() %>% 
  filter(EXCLUDE_FROM_NPT == "N") %>% 
  mutate(adult_count = rowSums(select(., BAND1_ADULT_COUNT, BAND23_ADULT_COUNT), na.rm = TRUE), 
         child_count = rowSums(select(., BAND1_CHILD_COUNT, BAND23_CHILD_COUNT), na.rm = TRUE), 
         total = rowSums(select(., BAND1_ADULT_COUNT, BAND23_ADULT_COUNT, BAND1_CHILD_COUNT, BAND23_CHILD_COUNT), na.rm = TRUE)) %>% 
  rename(month = YEAR_MONTH, COMMISSIONER_NAME = STP_Name)

# reformat names and region and commissioner names
names(npp_data) <- tolower(names(npp_data))

npp_data$region_name <- str_to_title(npp_data$region_name)
npp_data$commissioner_name <- str_to_title(npp_data$commissioner_name)
npp_data$commissioner_name <- substr(npp_data$commissioner_name,1,
                                     nchar(npp_data$commissioner_name)-21)
npp_data$commissioner_name <- substr(npp_data$commissioner_name,5,
                                     nchar(npp_data$commissioner_name))
npp_data$commissioner_name <- paste0(npp_data$commissioner_name,
                                     rep("ICB", nrow(npp_data)))
```

```{r dental 111 data}
#get dental 111 data from local file
dental_data_111 <- read_excel("N:/_Everyone/Primary Care Group/SMT_Dental Calendar data format/111_pathways_data/dental_data_111.xlsx")
```


```{r  DCP}

#extract DCP data
pull_dcp_data <- function(){
  
  con <- dbConnect(odbc::odbc(), "NCDR")
  
  sql <- "SELECT *
  FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_DCP] a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery(con, sql)
  DCP_data <- dbFetch(result)
  dbClearResult(result)
  
  DCP_data
}

#include contractors only in contractual_dataset
DCP_data<-pull_dcp_data()%>%
  inner_join(contractual_dataset,by=c('YEAR_MONTH','CONTRACT_NUMBER'))

#exclude those with UDA target less than 100 - exclude NA or blanks in DCP_DESC
contractors=UDA_calendar_data%>%
  select(month,contract_number)

DCP_data <-  DCP_data %>%
  inner_join(contractors, by=c('YEAR_MONTH'='month','CONTRACT_NUMBER'='contract_number'))

# calculate total FP17s and rename fields
DCP_data <- DCP_data %>%
  mutate(FP17_Current_Year_total =rowSums(
    select(., BAND_1_DELIVERED, BAND_2_DELIVERED, BAND_3_DELIVERED, BAND_URGENT_DELIVERED, BAND_OTHER_DELIVERED), 
    na.rm = TRUE))%>%
  dplyr::rename(Month = YEAR_MONTH,
      Contract_Number = `CONTRACT_NUMBER`,
           Contract.Type = `CONTRACT_TYPE`, 
           commissioner_name = `COMMISSIONER_NAME`,   
           DCP_description = `DCP_DESC`,
           UDA_Delivered_Current_Year =  `UDA_DELIVERED`,
           Band_1._UDA = `UDA_BAND_1_DELIVERED`,          
           Band_2._UDA = `UDA_BAND_2_DELIVERED` ,        
           Band_3._UDA = `UDA_BAND_3_DELIVERED`,
           Urgent_UDA = `UDA_BAND_URGENT_DELIVERED`,
           other_UDA = `UDA_BAND_OTHER_DELIVERED`, 
           FP17_Current_Year_B1= `BAND_1_DELIVERED`,       
           FP17_Current_Year_B2 = `BAND_2_DELIVERED`,       
           FP17_Current_Year_B3 = `BAND_3_DELIVERED`,       
           FP17_Current_Year_urgent = `BAND_URGENT_DELIVERED`,
           FP17_Current_Year_other= `BAND_OTHER_DELIVERED`)

# reformat region and commissioner names
DCP_data$Region <- str_to_title(DCP_data$Region_Name)
DCP_data$commissioner_name <- str_to_title(DCP_data$commissioner_name)
DCP_data$commissioner_name <- substr(DCP_data$commissioner_name,1,nchar(DCP_data$commissioner_name)-3)
DCP_data$commissioner_name <- paste0(DCP_data$commissioner_name, rep("ICB", nrow(DCP_data)))

# filter out Unknown DCP type
DCP_data <- DCP_data %>% 
  filter(!grepl("Unknown", DCP_description))

# reformat assisted/lead fields
DCP_data <- DCP_data %>% 
  mutate(DCP_DIR_DESC = case_when(
    DCP_DIR_DESC == "Dental Therapist" ~ "Therapist",
    DCP_DIR_DESC == "Dental Hygienist" ~ "Hygienist",
    DCP_DIR_DESC == "Clinical Technician" ~ "Technician", 
    DCP_DIR_DESC == "Dental Nurse" ~ "Dental_Nurse", 
    TRUE ~ DCP_DIR_DESC
  ))
```


```{r BPE}
pull_BPE_data <- function(){
  
  con <- dbConnect(odbc::odbc(), "NCDR")
  sql <- "SELECT * FROM [NHSE_Sandbox_PrimaryCareNHSContracts].[Dental].[Calendar_BPE]a
  LEFT JOIN (SELECT DISTINCT
  [Region_Name]
  ,[STP_Code]
  FROM [NHSE_Reference].[dbo].[tbl_Ref_ODS_Commissioner_Hierarchies]
  WHERE STP_Code NOT IN ('DUM001', 'DUM002', 'UNK','X24')) b ON a.[COMMISSIONER_CODE] = b.[STP_Code]"
  result <- dbSendQuery (con, sql)
  bpe_all <- dbFetch(result)
  dbClearResult(result)
  bpe_all
  
}

BPE_data<-pull_BPE_data()%>%
  inner_join(contractual_dataset,by=c('YEAR_MONTH','CONTRACT_NUMBER'))


# calculate and rename fields
BPE_data<-BPE_data %>%
  rename(commissioner_name=COMMISSIONER_NAME,
         Latest.Region.Description=Region_Name,
         Year_Month=YEAR_MONTH,
         Contract.Number=CONTRACT_NUMBER,
         Total.Form.Count=FORM_COUNT,
         Forms_with_Highest_BPE_Sextant_Score=HIGH_BPE_SCORE_COUNT,
         Highest.BPE.Sextant.Score=HIGH_BPE_SCORE_IND,
         Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_0_UDT=FRM_CNT_HBPE_0_1_UTD_0 ,
         Total_Form_Count_Highest_BPE_Sextant_Score_0_or_1_and_UDT_0_and_RRI_less_than_1_year=FRM_CNT_HBPE_0_1_UTD_0_SUB_12M ,
         )

# reformat region and commissioner names
BPE_data$Latest.Region.Description <- str_to_title(BPE_data$Latest.Region.Description)
BPE_data$commissioner_name <- str_to_title(BPE_data$commissioner_name)
BPE_data$commissioner_name <- substr(BPE_data$commissioner_name,1,
                                              nchar(BPE_data$commissioner_name)-3)
BPE_data$commissioner_name <- paste0(BPE_data$commissioner_name, 
                                              rep("ICB", nrow(BPE_data)))

```