sample.r

############################################################################

### FUNCTION TO EXTRACT DATA FROM SQL

get_sql_data <- function(driver = "SQL Server",
                         server = "sdl02-vm161",
                         db_name = "OpsDW",
                         query_text = which_query){
  
  output <- tryCatch({
  
      con = dbConnect(odbc(),
                    Driver = driver,
                    Server = server,
                    Database = db_name,
                    Trusted_Connection = "True")
    
      temp <- dbSendQuery(con, which_query)
    
      dat <- dbFetch(temp)
    
  }, error = function(cond) {
    message(paste("get_sql_data", "eror", cond, sep = "  -  "))
    return(NA)
  })
  
  if (is.null(output)) output <- dat
  return(output) 
  
}

###############################################################################
###############################################################################
### SIMPLE EXPONENTIAL SMOOTHING

SES_Forecast <- function(full_data = dat,
                         train_data = train_dat,
                         test_data = test_dat,
                         forecast_test_for = nrow(test_dat),
                         forecast_for = 12,
                         use_seed = TRUE,
                         mod_name = "forc_ses",
                         dim_name = dim_name,
                         dim_value = dim_value,
                         date_column = "date",
                         data_column = "value",
                         train_and_test = TRUE,
                         run_full_model = TRUE
                         ){
  
    message(paste0("Executing forecast: ", mod_name))
  
    if (use_seed) set.seed(master_seed)
  
    if(train_and_test){ 

        # train and test
        fit_ses <- ses(train_data[, get(data_column)], h=forecast_test_for, initial="simple")
        ses_test_fcast <- forecast(fit_ses)
        
        forecast_test_results[[mod_name]] <<- data.table(
                          forc_test_dates = seq.Date( 
                            DescTools::AddMonths(train_data[.N, get(date_column)], 1),
                            DescTools::AddMonths(train_data[.N, get(date_column)], forecast_test_for),
                            by = "month"),
                            forc_ses = as.numeric(ses_test_fcast$mean),
                          model = mod_name,
                          dim_name = dim_name,
                          dim_value = dim_value)
        
        forecast_test_accuracy_results[[mod_name]] <<- data.table(
                            accuracy(ses_test_fcast$mean[1:forecast_test_for], test_data[, get(data_column)]),
                            model = mod_name,
                            dim_name = dim_name,
                            dim_value = dim_value)
    }
  
    if(run_full_model){ 
          
        # run full forecast
        fit_ses <- ses(full_data[, get(data_column)], forecast_for, initial="simple")
        ses_full_forecast <- forecast(fit_ses, h=forecast_for)
        dat[.N, get(date_column)]
        forecast_full_results[[mod_name]] <<- data.table(
                            forc_dates = seq.Date( 
                                  DescTools::AddMonths(full_data[.N, get(date_column)], 1),
                                  DescTools::AddMonths(full_data[.N, get(date_column)], forecast_for),
                                  by = "month"),
                            forc_ses = as.numeric(ses_full_forecast$mean),
                            model = mod_name,
                            dim_name = dim_name,
                            dim_value = dim_value)
    }
}