cumulative 'difference'

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi All,

I have a situation here in mysql:
The table desc is:

Field     Type     Null     Key     Default     Extra
 --------  -------  -------  ------  ----------  --------
 dn_date   date              PRI     0000-00-00
 dn        int(10)           PRI     0
 ctr1      int(10)  YES              (null)
 ctr2      int(10)  YES              (null)
 ctr3      int(10)  YES              (null)
 ctr4      int(10)  YES              (null)
 isActive  char(3)

Now i want this kind of result

For one given DN value and date range, the expected output which i want
is :

FMR for the DN: 2280000
         CTR1+CTR2       Difference in readings
31/12/2004    X1                --
15/01/2005    X2               X2-x1
31/01/2005    X3               X3-x2
15/02/2005    X4               X4-x3
28/02/2005    X5               X5-x4

And so on..

That is user enters a range of date and a dn value and he gets the
above output.

Thanks for any suggestions/directions


Re: cumulative 'difference' wrote:
Quoted text here. Click to load it

In general, anytime you need to compare or calculate values from
different records of a table, you need to do a self-join.

Also, you need to do something tricky to find the "previous" record,
since they increment by an irregular pattern.  That is, you can't just
subtract 15 days from one record's dn_date to find the previous record.
  Try something like this (though it is untested):

SELECT d1.dn_date, d1.crt1 + d1.ctr2AS X,
   (d1.crt1 + d1.crt2) - (d2.crt1 + d2.crt2) AS difference_in_readings
FROM desc AS d1 LEFT OUTER JOIN desc AS d2
   ON d2.dn_date = (
     SELECT MAX(ds.dn_date) FROM desc AS ds
     WHERE ds.dn_date >= '$user_min_date' AND ds.dn_date < d1.dn_date)
WHERE d1.dn_date BETWEEN '$user_min_date' AND '$user_max_date'

Bill K.

Site Timeline