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 |
|
Fletch
Starting Member
29 Posts |
 Posted - 2005-10-07 : 05:56:48
|
| I have a chart showing data over a 24 hour period (00:00 -> 23:59) for one or more sets of data.There is data every 20 minutes or so for each device, except it's possible that #1 will have data from midnight to midnight but #2 will only have data from 10:00 until 12:00It appears that if #1 has a full set of data all appears well; smooth-ish line charts for each set of data. If #1 is incomplete (say 10:00 - 12:00 and #2 is 00:00 - 23:59) all the data is displayed but the chart insists on 'looping' the line back round again.I've managed to adjust all the datatime's back to 01/01/2000 to allow me to put a min and max on the chart (of 01/01/2000 00:00 - 01/01/2000 23:59) but it still insists on showing the line looping back to the start again!Any advice? There must be a simple answer... |
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-10-07 : 06:06:55
|
How about having a time dimension (a table holding an entry for each possible time interval) and left joining the values to this table (treating the null values as 0) this way you will have an entry for each time slot allbeit 0 for those where there are no entries.Duane.  |
 |
|
|
Fletch
Starting Member
29 Posts |
Posted - 2005-10-07 : 06:10:39
|
| Thanks Ditch.won't work I'm afriad as '0' is a valid value (temperature). I need to differentiate between receiving a 0 and receiving no data.Steve. |
|
|
|
ditch
Master Smack Fu Yak Hacker
1466 Posts |
Posted - 2005-10-07 : 06:15:01
|
| mmmmmm.... and I suppose it will get a bit too complex and ugly if you try and average the tempreture values for the time intervals where you have missing data?Duane. |
|
|
|
Fletch
Starting Member
29 Posts |
Posted - 2005-10-07 : 06:29:09
|
| non-starter.missing temperatures are potentially as serious as temperatures outside a specified range - I need to take action on them.(additionally, series one might be every twenty minutes at hh:20, hh:40 etc. and series two might be every 10 minutes at hh:12 ...) |
|
|
|
Fletch
Starting Member
29 Posts |
Posted - 2005-10-07 : 06:59:41
|
| SOLUTION:(not perfect - but works).It looks like the problem is related to the time of the FIRST time of the FIRST series, rather than the range (10:00-12:00 from the e.g. above).By grouping the data by sensor but sorting it by time, the first value returned is the earliest in the day - thus the chart displays it properly. This is not perfect for me as I would like the Legend in alphabetical order but a small price to pay for a correct chart.Presumably the charting component is doing something clever; if the first series starts at 10:00am, any value earlier than 10:00 am must be the following day, so loop round?Thanks for your help guys.Steve. |
|
|
|
|
|
|
Chart problem with datetime x axis