[R] Reference a variable inside a string and another for object assingments

[Kenneth Rose]

Hi R community

I copied a bit of my R code that gets some data from a database. You
won't be able to run the code, but I am a beginner so you will
probably understand what going on.

I would like to make a variable I can refer to inside the sqlQuery.
Instead of writing the start date and time (ex SP.lokaldatotid >=
'2005-01-01 00:00:00') inside the query I would like to define it in
the beginning of the code, so I don't have to fiddle with a lot of
dates each time I wan't to change it. I would like to do this for a
few of the variables and maybe even make a list/array I can loop
through, so I don't have to write the same code multiple times (for
SYS and DK1).

I have searched for a solution for two days now, but I am not sure
what it's called and are probably writing the wrong queries :-)


Thank you for your help!

Kenneth

My code:

library(xts)
library(RODBC)


#Define channnel (i configured my own SYSTEM-DNS, via ODBC)
ch <- odbcConnect("DI2")

#####################################################################
############################## GET DATA    ##########################
#####################################################################

############################## SYSTEM spot ##########################
# Hent data fra SQL Server
sys <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                           "WHERE (SP.omraade_id= 0 AND
SP.lokaldatotid >= '2005-01-01 00:00:00')"))
#Definer dato og tid kolonne
sys$lokaldatotid <- as.POSIXct(sys$lokaldatotid)

#Make a XTS object
sys_xts <- xts(sys[,-1], order.by=sys[,1])

# Recalculate data from hours to daily, monthly and yearly averages
sys_xts_daily <- apply.daily(sys_xts, FUN=mean)
sys_xts_monthly <- apply.monthly(sys_xts, FUN=mean)
sys_xts_yearly <- apply.yearly(sys_xts, FUN=mean)


############################## DK1 spot #############################
# Hent data fra SQL Server
dk1 <- sqlQuery (ch, paste("SELECT  SP.lokaldatotid, SP.pris FROM
DataIndsamling2.dbo.SpotPriser SP",
                           "WHERE (SP.omraade_id= 5 AND
SP.lokaldatotid >= '2005-01-01 00:00:00')"))
#Definer dato og tid kolonne
dk1$lokaldatotid <- as.POSIXct(dk1$lokaldatotid)

#Lav om til xts object
dk1_xts <- xts(dk1[,-1], order.by=dk1[,1])

#Data omregnet fra time ->> daglig, måned, årlige gennemsnit
dk1_xts_daily <- apply.daily(dk1_xts, FUN=mean)
dk1_xts_monthly <- apply.monthly(dk1_xts, FUN=mean)
dk1_xts_yearly <- apply.yearly(dk1_xts, FUN=mean)