Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
punkaholic
Starting Member
1 Post |
Posted - 2013-11-05 : 07:56:09
|
Hi I'm new to SQL. I'm using VBA to extract data from the SQL 2008 server and put it in an excel sheet. I'm averaging the data per hour with the intention to make a weekly graph out of it. Unfortunately at some timeperiods no data has been recorded and thus no timestamp has been created. However I still need those timestamps to make a graph with a continuous time interval of an hour. Below is an example of such a timegap and also the sql string I am using. Is there a way to fill in this timegap?kind regards,MP2 25/10/2013 23:00MP2 26/10/2013 0:00MP2 26/10/2013 2:39MP2 26/10/2013 3:00"SELECT MonitoringLogs.MPName,Min(MonitoringLogs.TimeStamp)as Timestamp,Avg(MonitoringLogs.Easting)as Easting, & Avg(MonitoringLogs.Northing)as Northing,Avg(MonitoringLogs.Height)as Height,Avg(MonitoringLogs.Pitch)as Pitch,Avg(MonitoringLogs.Roll)as Roll & _FROM MonitoringLogs & _WHERE MonitoringLogs.TimeStamp>= {ts " & Format(startdate, "\'yyyy-mm-dd hh:mm:ss\'") & " }& _ AND MonitoringLogs.TimeStamp< {ts " & Format(enddate, "\'yyyy-mm-dd hh:mm:ss\'") & " }& _AND MonitoringLogs.Fix = 4 & _ GROUP BY MONTH(MonitoringLogs.TimeStamp),DAY(MonitoringLogs.TimeStamp),{fn HOUR(MonitoringLogs.TimeStamp)},MonitoringLogs.MPName & _ORDER BY MonitoringLogs.MPName,Timestamp " |
|
|
|
|
|
|