List:General Discussion« Previous MessageNext Message »
From:Daevid Vincent Date:February 11 2005 8:31pm
Subject:Need help with historic aggregation of data
View as plain text  
I need to get the aggregate data from various tables for a report.

The idea is that we audit devices daily on a schedule, and also allow users
to audit the devices by choosing certain tests to run. It is also the case
that new tests are added daily. So the scheduled test today has more tests
than yesterdays and that has more than the day before's, etc.

I want to get a report that shows ALL tests ever run on the device in it's
lifetime, but only the most recent of each test (and the date it was from).

So if I ran tests like this:

Date	 Device   Test    Result
-----  ------   -----   ------

02/01    1       100     [scheduled] blah blah blah...
02/01    1       101     [scheduled] blah blah blah...
02/01    1       102     [scheduled] blah blah blah...
02/01    1       105	 [one off] foo foo foo...

02/02    1       100     [scheduled] blah blah blah...
02/02    1       101     [scheduled] blah blah blah...
02/02    1       102     [scheduled] blah blah blah...
02/02    1       103     [scheduled] ble ble ble...
02/02    1       106	 [one off] bar bar bar...

02/03    1       100     [scheduled] blah blah blah...
02/03    1       101     [scheduled] blah blah blah...
02/03    1       102     [scheduled] blah blah blah...
02/03    1       103     [scheduled] ble ble ble...
02/03    1       104     [scheduled] blo blo blo...

02/01    2       100     [scheduled] blah blah blah...
02/01    2       101     [scheduled] blah blah blah...
02/01    2       102     [scheduled] blah blah blah...
02/01    2       106	 [one off] bar bar bar...
	... Etc ...

What I'd expect to get back for device 1 is

Test	Date
----	-----
100	02/03   this is more current than others
101   02/03	  this is more current than others
102   02/03   this is more current than others
103   02/03   this is more current than others
104   02/03   this is more current than others
105   02/01   since this was run long ago once
106   02/02   since this was ran recently


My actual tables are pretty huge, and I'll spare you them. I also am coding
this in PHP, in case I need to split this task up somehow. We are using
v4.0.18 and can't change.

I'm hoping there is some magic incantation of MAX(), GROUP BY, DISTINCT,
that will harvest this info for me.

Thanks in advance,

Daevid.








Thread
Need help with historic aggregation of dataDaevid Vincent11 Feb
  • Re: Need help with historic aggregation of dataHomam S.A.11 Feb
RE: Need help with historic aggregation of dataTom Crimmins11 Feb