cumulative 'difference'

[sachin.prasad]

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

Sachin

[Bill Karwin]

sa***********@gmail.com wrote:

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

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'

Regards,
Bill K.