Well maybe not for you guru's...

MySQL 5.0.45

I have a table that has years of certification data that ties it to
it's parent with an index number...I want to iterate through the
records, calculate a percentage of change, and place it in a field
(Deviation) that has been previously created:

ID = 1 CalibrationDate = 2001-02-14 Volume = 15.123456
Deviation = NULL
ID = 1 CalibrationDate = 2003-01-23 Volume = 15.123547
Deviation = 0.0006
ID = 1 CalibrationDate = 2005-03-18 Volume = 15.123598
Deviation = 0.0003

After the Deviation has been calculated and placed, I want to compare
each with the previous to determine if the the accepted tolerance has
been exceeded (>=0.04%) and if it has place a "Y" in another
previously created field (OutOfTol)...

I'm no database expert especially with problems of this nature...Any
will be greatly appreciated...

Thanks in advance for your attention...!!!...

Dave

On Thu, 14 Feb 2008 20:14:17 +0100, <crashbangboom@gmail.com> wrote:
> Well maybe not for you guru's...

In future, you might want to use a better topic then 'BIG PROBLEM'. The
fact you have a question is usually clear by starting a thread, having a
descriptive subject draws more people in.


> MySQL 5.0.45
>
> I have a table that has years of certification data that ties it to
> it's parent with an index number...I want to iterate through the
> records, calculate a percentage of change, and place it in a field
> (Deviation) that has been previously created:
>
> ID = 1 CalibrationDate = 2001-02-14 Volume = 15.123456
> Deviation = NULL
> ID = 1 CalibrationDate = 2003-01-23 Volume = 15.123547
> Deviation = 0.0006

Euhm, deviation is about 0.000006612%

> ID = 1 CalibrationDate = 2005-03-18 Volume = 15.123598
> Deviation = 0.0003

> After the Deviation has been calculated and placed, I want to compare
> each with the previous to determine if the the accepted tolerance has
> been exceeded (>=0.04%) and if it has place a "Y" in another
> previously created field (OutOfTol)...
>

I assume a deviation is to be calculated as a percentage change from the
last (highest date) volume ((old.volume - new.Volume) / old.volume) * 100,
of the same ID? If CalibrationDate is never NULL, you might want to use
this (be warned: untested):

UPDATE tablename o
JOIN tablename j
ON o.ID = j.ID
AND j.CalibrationDate < o.CalibrationDate
LEFT JOIN tablename x
ON o.ID = x.ID
AND x.CalibrationDate > o.CalibrationDate
SET o.Deviation = ((o.Volume - j.Volume)/o.Volume) * 100,
o.OutOfTol = IF(((o.Volume - j.Volume)/o.Volume) > 0.0004,'Y','N')
WHERE x.ID IS NULL
--
Rik Wasmus