How to change the where clause and append at the same time using SELECT statement in SQL Server?

Question

sect<-c("Healthcare","Basic Materials","Utilities","Financial Services","Technology","Consumer" 
"Defensive","Industrials","Communication Services","Energy","Real Estate","Consumer 
Cyclical","NULL")

mcap<-c("3 - Large","2 - Mid","1 - Small")

df_total = data.frame()
start <- as.Date("01-01-14",format="%d-%m-%y")
end   <- as.Date("18-03-20",format="%d-%m-%y")
theDate <- start

while (theDate <= end){
  for (value1 in sect){
    for (value2 in mcap){
        date=theDate
        sector<-value1
        marketcap1<-value2
        newquery("Select * from table where date='%s' and sector='%s' and 
        marketcap='%s'",date,sector,marketcap1)
        topdemo <- sqlQuery(dbhandle,newquery)
        df=data.frame(topdemo)
        df_total <- rbind(df_total,df)

 }
}
theDate <- theDate + 1 
}

How to loop this code in SQL Server so that the execution time is not too long and append it to the same select statement? I need to loop the query in SQL so that it goes through every date, market cap, and sector and compute certain things. At last, the appended query will be written in the database.

Note: the Select query shown above is just a sample query. In my work, I'm doing a lot of computations using SQL.

Note: I cant use 'between' or 'In' command because in my computation Im taking an average of a column on that particular date, sector, and market cap. If I use 'between' for dates it is going to take an average of all the dates given.

Answer 1

You can use the BETWEEN operator in SQL to check for a range of dates.

where date between '%s' and '%s'

And you can use the IN operator to check for presence of item on a list.

and sector in ('%s', '%s', ...)

You can produce a character string with all your sectors in R, surrounded by double quotes and separated by commas, by doing the following statement, so it's easy to insert it in your SQL query.

sector.list <- paste0(sapply(sect, function(x) paste0("'", x, "'")), collapse = ", ")

print(sector.list)

Output

[1] "'Healthcare', 'Basic Materials', 'Utilities', 'Financial Services', 'Technology', 'Consumer', 'Defensive', 'Industrials', 'Communication Services', 'Energy', 'Real Estate', 'Consumer Cyclical', 'NULL'"

Same thing applies for mcap.

**** Regarding extracting the mean or other aggregates *** Now if you don't want all the details from the database, but simply the mean or a given column, you can group your data by the desired fields (date, sector and mcap) and extract the average like:

SELECT avg(desired_column)
FROM (... your query here...)
GROUP BY data, sector, mcap

Doing an introductory course on SQL is highly advised.