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) ______________________________________________ R-help@r-project.org mailing list https://stat.ethz.ch/mailman/listinfo/r-help PLEASE do read the posting guide http://www.R-project.org/posting-guide.html and provide commented, minimal, self-contained, reproducible code.