R and SQL referencing inside a Query

Question

I have connected to the SQL server through R and have the following code. I want to create a reference outside the query for the Factsetdate (see bold below). This would mean to take out the SELECT DATEADD.... components out the query and add it to a variable called Date1 for example. Is there a way to do this? Thanks

SQLCommandEOM<-data.frame(sqlQuery(myConn, 
"SELECT fc.[FactSet Fund Code], fc.FactsetDate, fc.[Asset Class], fc.[Fund Manager],
 d.Id AS DeskID, d.[Name] AS DeskName, fc.[% Factor Risk], fc.[Predicted Tracking Error],
 fc.[Portfolio Predicted Beta]  

 FROM [PortfolioAnalytics].[Data_Factset].[FundChar] fc 
 LEFT OUTER JOIN Data_Axioma.FactSetAccount fsa ON fc.FactsetAccountCode = REPLACE(fsa.Code, '_', '') 
 LEFT OUTER JOIN dbo.Portfolio p ON fsa.PortfolioId = p.Id 
 LEFT OUTER JOIN dbo.Mandate m ON p.UnderlyingId = m.id 
 LEFT OUTER JOIN dbo.Desk d ON m.DeskId = d.Id 
 WHERE IncludesCash = 1 
 AND [FactSet Fund Code] IS NOT NULL 
 AND [Fund Manager] IS NOT NULL 
 AND [Asset Class] IS NOT NULL 
 AND FactsetDate = **(SELECT DATEADD(d,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)))**
                            ORDER BY [Predicted Tracking Error] DESC"))

Answer 1

This is how to paste a string from an R object into a SQL query within R:

sql       <- paste("select *
                     from MyDB.dbo.DIM_Person s
                         where OppSFId in ('",
                         paste(sept$OppSFId,collapse = "','"),"')",sep = "")
sept.person <- sqlQuery(db,sql)

where my character vector is called sept$OppSFId.

So, if I understand your example correctly, your character vector will be called Date1 and you want to substitute it in like this:

SQLCommandEOM <- data.frame(sqlQuery(myConn, paste('"SELECT fc.[FactSet Fund Code], fc.FactsetDate, fc.[Asset Class], fc.[Fund Manager],
 d.Id AS DeskID, d.[Name] AS DeskName, fc.[% Factor Risk], fc.[Predicted Tracking Error],
 fc.[Portfolio Predicted Beta]  

 FROM [PortfolioAnalytics].[Data_Factset].[FundChar] fc 
 LEFT OUTER JOIN Data_Axioma.FactSetAccount fsa ON fc.FactsetAccountCode = REPLACE(fsa.Code, '_', '') 
 LEFT OUTER JOIN dbo.Portfolio p ON fsa.PortfolioId = p.Id 
 LEFT OUTER JOIN dbo.Mandate m ON p.UnderlyingId = m.id 
 LEFT OUTER JOIN dbo.Desk d ON m.DeskId = d.Id 
 WHERE IncludesCash = 1 
 AND [FactSet Fund Code] IS NOT NULL 
 AND [Fund Manager] IS NOT NULL 
 AND [Asset Class] IS NOT NULL 
 AND FactsetDate =', Date1,
'ORDER BY [Predicted Tracking Error] DESC"))')