CrossTab by hand

[Grimfort]

O this is a nice mission :). I need opinion as much as code in this issue, so thinking hats on please.

 

I have a dataset, which I am using to fill a graph with. The graph is a time based setup in which I need to return results from a database based on certain (changable) paramaters. Heres an example:

 

EntryName EntryDate EntryValue

-----------------------------------------------------------

PC1 1/1/2003 14:00 5

PC1 1/1/2003 14:01 5

PC1 1/1/2003 14:02 4

PC1 1/1/2003 14:03 5

PC2 1/1/2003 14:00 6

PC2 1/1/2003 14:01 6

PC2 1/1/2003 14:02 6

PC2 1/1/2003 14:03 6

 

 

The graph will have 2 series, one for PC1 and one for PC2.

As you can see that kind of data willl graph quite easily, every minute a reading is taken, and saved to the database. The problem comes when a reading (or many) is missed.

 

A graph normally crosstabs the data and any entires that are in one series but not in another are blanked out (which is great), and you get gaps in the graph.

 

I want to be able to "crosstab" the data manually myself, in order to make sure there is a value (be it zero) for a constant range of dates. Say I pick 1/1/2003 all day, with an interval of 5 mins. I need a way to look at the records in each 5 min period of the whole and get the average value of that period, and stick it into the graph.

I may also want to get the data on an hourly scale (for a larger overview), so in each hour I need to be able to get the average value of all the records in that hour, and again, throw it at the graph.

 

 

Tis long winded I know, so thanks for any help, im hoping some1 has done something similar. So to the questions:

 

Should my graphing tool do this crosstab on my behalf ?

What graphing tools do you use and recommend that can do it ?

Is there any way to use the .net data functions to be able to do this easier ?

Could this be done by my database (its SQL or access or anything else we decide to move to, so must be generic functions) ?

Any other tips ?

 

Thanks in advance

[Derek Stone]

Strictly speaking it wouldn't be too difficult to generate a chart yourself, without any help from charting software such as Dundas Chart or ComponentOne's WebChart. Finding an average for a 5 minute interval is straightforward basic algebra.

avg(xseries) = (x1 + x2 + ... + xn) / n

Simply iterate through your data series with a For/Next loop with an interval of 5, graphing each point as you go along.

[Grimfort]

Yer, I have a good idea how to do the actual working out by hand, just wanted to know if any1 can think of an easier way (ie get the data functions to do it).