i have code below trying use create table datetime stamp every minute of year, left join table, , use tsql create new field in final select statement returned. sql runs fine in sql server, when try run in rodbc errors, empty character vector, or character vector sql server error messages. i've seen posts using temp tables in rodbc , separating them "go", or in separate sqlquery statements. i've tried unsuccessfully separating steps "go", , i'm not sure separating statement several sqlquery statements work in case since i'd use both temp tables in final select statement tsql functions. advice appreciated.
code: library("forecast") library("tseries") library("sqldf") library("manipulate") library("caret") library("qdaptools") library("rodbc") library("dplyr") library("xts") con<-odbcconnect("rsqlconnect") dfetest<-sqlquery(con," create table #tempfm (dt datetime not null); declare @datestart datetime = '2015-01-01 00:00', @dateend datetime = '2015-12-31 23:59'; while @datestart <= @dateend begin insert #tempfm (dt) select dateadd(minute, number, @datestart) master..spt_values type = 'p' , number between 0 , 1439 , dateadd(minute, number, @datestart) <= @dateend; set @datestart = dateadd(minute, 1440, @datestart); end select dtime, 1 cv #tempp tablem select dateadd(hh,datepart(hour,dt), dateadd(mi,(datepart(minute,dt)/15)*15,cast(cast(dt date)as datetime))) vtq, * #tempfm f left join #tempp p on p.dtime = f.dt ") data: dput(tablem[1:100,]) structure(list(dtime = structure(c(1435505100, 1424580900, 1423598220, 1426616460, 1440028200, 1449095520, 1426370640, 1423697880, 1440517860, 1426479960, 1428707940, 1450308780, 1449771840, 1437770880, 1433912820, 1449501900, 1427121780, 1430257500, 1446993480, 1440373200, 1432256100, 1426721700, 1449449340, 1422559320, 1423162920, 1422683580, 1423545300, 1424242200, 1445704920, 1447931340, 1443834480, 1450638300, 1446287220, 1451246520, 1449158280, 1447267260, 1448101560, 1449361860, 1450958520, 1447351680, 1448352120, 1449038040, 1447534680, 1448908620, 1440340980, 1443400980, 1448952120, 1448851680, 1448856540, 1447201440, 1448128440, 1448061660, 1438615980, 1444851960, 1447698180, 1450836060, 1451323080, 1447693140, 1447981980, 1440027300, 1449005220, 1446873960, 1451392260, 1449064500, 1450879140, 1451177100, 1450883100, 1449081840, 1448165040, 1443192720, 1442772900, 1447801140, 1444959300, 1446919980, 1447376220, 1447702980, 1450558080, 1450360620, 1443759360, 1440593340, 1450672440, 1441950840, 1449247140, 1447280580, 1449704160, 1451545800, 1445292540, 1449862440, 1450046580, 1449707520, 1450376520, 1450904100, 1447016700, 1445739720, 1449717180, 1445973000, 1438405920, 1450006500, 1443343800, 1449465540), class = c("posixct", "posixt"), tzone = ""), cv = c(1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l, 1l)), .names = c("dtime", "cv"), row.names = c(na, 100l), class = "data.frame")
Comments
Post a Comment