cumulative "difference"

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

Re: cumulative "difference"

sachin.prasad@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.