R- How to do a loop on a list and output different dataframes

Question

I'm attempting to create a loop in R that will use a vector of dates, run them through a loop that includes a SQL query, and then generate a separate dataframe for each output. Here is as far as I've gotten:

library(RODBC)

dvect <- as.Date("2015-04-13") + 0:2   
d <- list()
for(i in list(dvect)){

    queryData <- sqlQuery(myconn, paste("SELECT
        WQ_hour,
        sum(calls) as calls
        FROM database
        WHERE DDATE = '", i,"'
        GROUP BY 1
        ", sep = ""))

    d[i] <- rbind(d, queryData)
}

From what I can tell, the query portion of the code runs fine since I've tested it by itself. Where I'm stumbling is the last line where I try to save the contents of each loop through the query separately with each having a label of the date that was used in the loop.

I'd appreciate any help. I've only been using R consistently for about 2 months now so I'm definitely open to alternative ways of doing this that are cleaner and more efficient.

Thanks.

Answer 1

I'd suggest making the SQL query a function, and use lapply to apply it and return your result as a list.

userSQLquery = function(i) {
  sqlQuery(myconn, paste("SELECT
          WQ_hour,
          sum(calls) as calls
          FROM database
          WHERE DDATE = '", i,"'
          GROUP BY 1
          ", sep = ""))
}

dvect = as.Date("2015-04-13") + 0:2    
d = as.list(1:length(dvect))
names(d) = dvect

lapply(d, userSQLquery)

I have very little experience with SQL though, so this may not work. Maybe it could start you off?