Problem with Update query

Hi there.

I need your appreciated help.

I have this records in the mysql table listfiles_2011_11_25:


CODE_A	myDate	max_STATUS	max_date	INC_H2	INC_CH4	INC_C2H6	INC_C2H4	INC_C2H2	INC_CO	INC_CO2
5550	2005-10-27	ALERT	2009-07-28	-0.022	0.067	0.044	0.089	0.022	0.422	11.267
5550	2003-10-07	OK	2005-10-27	-3.500	-5.917	-4.500	-4.625	0.125	-5.542	-60.250

This is the output with this query:


SELECT  
  t.CODE_A 
, t.myDate

, m.max_STATUS 
, m.max_date 

, m.max_H2_PPM , t.H2_PPM 
, FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_H2 

, m.max_CH4_PPM , t.CH4_PPM 
, FORMAT((max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CH4 

, m.max_C2H6_PPM , t.C2H6_PPM 
, FORMAT((max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H6 

, m.max_C2H4_PPM , t.C2H4_PPM 
, FORMAT((max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H4 

, m.max_C2H2_PPM , t.C2H2_PPM 
, FORMAT((max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_C2H2 

, m.max_CO_PPM , t.CO_PPM , FORMAT((max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CO 

, m.max_CO2_PPM , t.CO2_PPM , FORMAT((max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
, EXTRACT(YEAR_MONTH FROM t.myDate)),3) inc_CO2 

FROM 
( SELECT 
  CODE_A 
, MAX(myDate) max_date
, MAX(STATUS) max_STATUS 
, MAX(H2_PPM) max_H2_PPM 
, MAX(CH4_PPM) max_CH4_PPM 
, MAX(C2H6_PPM) max_C2H6_PPM 
, MAX(C2H4_PPM) max_C2H4_PPM 
, MAX(C2H2_PPM) max_C2H2_PPM 
, MAX(CO_PPM) max_CO_PPM 
, MAX(CO2_PPM) max_CO2_PPM 
FROM 
listfiles_2011_11_25 
GROUP BY CODE_A, myDate) m 
INNER JOIN listfiles_2011_11_25 t ON m.CODE_A = t.CODE_A 
AND (m.max_date > t.myDate)
AND t.CODE_A = '5550' 
GROUP BY t.CODE_A, myDate
ORDER BY t.ID DESC;

I have to compare values inc_H2, inc_CH4, inc_C2H6, inc_C2H4, inc_C2H2, inc_CO, inc_CO2 with this values:


inc_H2	inc_CH4	inc_C2H6	inc_C2H4	inc_C2H2	inc_CO	inc_CO2
11	10	8		13		4		90	850

If only one value is out of range I need update the field' STATUS of the mysql table listfiles_2011_11_25withALERTelse it'sOK`.

I try this query, but even nothing value is out of range the field' STATUS not change from ALERTtook` in the first record.

What wrong?
Can you help me?


UPDATE listfiles_2011_11_25 t
INNER JOIN

( SELECT 
              CODE_A
            , MAX(STATUS) max_STATUS
            , MAX(myDate) max_date
            , MAX(H2_PPM) max_H2_PPM
            , MAX(CH4_PPM) max_CH4_PPM
            , MAX(C2H6_PPM) max_C2H6_PPM
            , MAX(C2H4_PPM) max_C2H4_PPM
            , MAX(C2H2_PPM) max_C2H2_PPM
            , MAX(CO_PPM) max_CO_PPM
            , MAX(CO2_PPM) max_CO2_PPM

FROM listfiles_2011_11_25
GROUP BY CODE_A, myDate) m

ON   m.CODE_A = t.CODE_A 
AND (m.max_date > t.myDate)
AND (t.CODE_A <>'' AND t.CODE_A <> '0')

SET
     , t.STATUS =
       IF(
       FORMAT((max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 11 OR 
       
       FORMAT((max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 10 OR 
       
       FORMAT((max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 8 OR 

       FORMAT((max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 13 OR 

       FORMAT((max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 4 OR 

       FORMAT((max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 90 OR 

       FORMAT((max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)),3) > 850
     , 'ALERT', 'ok')

WHERE t.CODE_A like '%5550%';

if your query runs but doesn’t update anything, this means that the data values you are checking are all okay

either that, or your checking calculations are incorrect

why are you using FORMAT with decimal places, when your comparisons are to whole numbers?

e.g. FORMAT((max_H2_PPM - t.H2_PPM) / PERIOD_DIFF(…),3) > 11

by the way, if you specify this in your WHERE clause –

… WHERE t.CODE_A like ‘%5550%’;

then you don’t need this in your join –

… AND (t.CODE_A <>‘’ AND t.CODE_A <> ‘0’)

Hi Sir: I record your suggestions and try this query:

UPDATE listfiles_2011_11_25 t
INNER JOIN

( SELECT 
              CODE_A
            , MAX(STATUS) max_STATUS
            , MAX(myDate) max_date
            , MAX(H2_PPM) max_H2_PPM
            , MAX(CH4_PPM) max_CH4_PPM
            , MAX(C2H6_PPM) max_C2H6_PPM
            , MAX(C2H4_PPM) max_C2H4_PPM
            , MAX(C2H2_PPM) max_C2H2_PPM
            , MAX(CO_PPM) max_CO_PPM
            , MAX(CO2_PPM) max_CO2_PPM

FROM listfiles_2011_11_25
GROUP BY CODE_A, myDate) m

ON   m.CODE_A = t.CODE_A 
AND (m.max_date > t.myDate)

SET
     , t.STATUS =
       IF(
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR 
       
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR 
       
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR 

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR 

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR 

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR 

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850
     , 'ALERT', 'ok')

WHERE t.CODE_A like '%5550%';

Output:
Affected rows: 0
Time: 1.781ms

But if I have this row:

CODE_A	myDate	max_STATUS	max_date	INC_H2	INC_CH4	INC_C2H6	INC_C2H4	INC_C2H2	INC_CO	INC_CO2
5550	2005-10-27	ALERT	2009-07-28	-0.022	0.067	0.044	0.089	0.022	0.422	11.267

And all values inc... are in this range:

inc_H2	inc_CH4	inc_C2H6	inc_C2H4	inc_C2H2	inc_CO	inc_CO2
11	10	8		13		4		90	850

Why the STATUS not change in this row from ALERT to OK ?

I need to compare rows in table listfiles_2011_11_25 for CODE_A.

i have no idea, because i don’t know your data

by the way, why do you have GROUP BY BY CODE_A, myDate in the “m” subquery?

you do realize that MAX(mydate) can have only one value for each mydate, right?

It’s true…, but I don’t have change in the output of the query update:


UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
AND (m.max_date > t.myDate) 

SET 
     , t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR  

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850 
     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';  

Output:
Affected rows: 0
Time: 1.781ms

This is my table mysql:


/*
Navicat MySQL Data Transfer

Source Server         : _myServer
Source Server Version : 50045
Source Host           : localhost:3306
Source Database       : _mydb

Target Server Type    : MYSQL
Target Server Version : 50045
File Encoding         : 65001

Date: 2011-11-25 16:48:03
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `listfiles_2011_11_25_copy`
-- ----------------------------
DROP TABLE IF EXISTS `listfiles_2011_11_25_copy`;
CREATE TABLE `listfiles_2011_11_25_copy` (
  `ID` int(10) NOT NULL auto_increment,
  `CODE_A` varchar(255) default NULL,
  `myDate` date default NULL,
  `CO_PPM` decimal(10,2) default NULL,
  `CO2_PPM` decimal(10,2) default NULL,
  `H2_PPM` decimal(10,2) default NULL,
  `CH4_PPM` decimal(10,2) default NULL,
  `C2H6_PPM` decimal(10,2) default NULL,
  `C2H4_PPM` decimal(10,2) default NULL,
  `C2H2_PPM` decimal(10,2) default NULL,
  `STATUS` varchar(10) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6012 DEFAULT CHARSET=latin1;

-- ----------------------------
-- Records of listfiles_2011_11_25_copy
-- ----------------------------
INSERT INTO listfiles_2011_11_25_copy VALUES ('2309', '5550', '2003-10-07', '223.00', '2256.00', '97.00', '144.00', '114.00', '114.00', '0.00', 'ok');
INSERT INTO listfiles_2011_11_25_copy VALUES ('3408', '5550', '2005-10-27', '90.00', '810.00', '13.00', '2.00', '6.00', '3.00', '3.00', 'ok');
INSERT INTO listfiles_2011_11_25_copy VALUES ('6011', '5550', '2009-07-28', '109.00', '1317.00', '12.00', '5.00', '8.00', '7.00', '4.00', 'alert');

Error copy/paste in the query update of the last post; this is correct.
Sorry Sir.

UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
AND (m.max_date > t.myDate) 

SET 
       t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR  

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850 
     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';  

okay, i looked at your test data

SELECT t.CODE_A
     , t.id
     , t.status
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                   EXTRACT(YEAR_MONTH FROM t.myDate))   AS period_diff
     , (max_H2_PPM-t.H2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_11        
     , (max_CH4_PPM-t.CH4_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_10        
     , (max_C2H6_PPM-t.C2H6_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_8
     , (max_C2H4_PPM-t.C2H4_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_13
     , (max_C2H2_PPM-t.C2H2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_4
     , (max_CO_PPM-t.CO_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_90
     , (max_CO2_PPM-t.CO2_PPM) /
         PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date),
                     EXTRACT(YEAR_MONTH FROM t.myDate))   AS diff_850 
  FROM ( SELECT CODE_A 
              , MAX(STATUS) max_STATUS 
              , MAX(myDate) max_date 
              , MAX(H2_PPM) max_H2_PPM 
              , MAX(CH4_PPM) max_CH4_PPM 
              , MAX(C2H6_PPM) max_C2H6_PPM 
              , MAX(C2H4_PPM) max_C2H4_PPM 
              , MAX(C2H2_PPM) max_C2H2_PPM 
              , MAX(CO_PPM) max_CO_PPM 
              , MAX(CO2_PPM) max_CO2_PPM 
           FROM listfiles_2011_11_25_copy 
         GROUP 
             BY CODE_A ) m 
INNER
  JOIN listfiles_2011_11_25_copy AS t
    ON m.CODE_A = t.CODE_A     
   AND m.max_date > t.myDate 


CODE_A   id   status   period_diff   diff_11    diff_10    diff_8     diff_13    diff_4     diff_90   diff_850
5550   2309   ok           69       0.000000   0.000000   0.000000   0.000000   0.057971   0.000000   0.000000
5550   3408   ok           45       1.866667   3.155556   2.400000   2.466667   0.022222   2.955556   32.133333

if you’re wondering where id 6011 went, you filtered it out using this –

   
   AND m.max_date > t.myDate 

Thank Sir I understand your test data and now I filter with:


   AND m.max_date >= t.myDate 

Remove this condition, the output is identic.

But in the new output the id 6011 is not examined… this is wrong… perhaps my solution is wrong… you have some ideas for my problem? thanks for your help.


CODE_A	id	status	period_diff	diff_11		diff_10		diff_8		diff_13		diff_4		diff_90		diff_850
5550	2309	ok		69	0,000000	0,000000	0,000000	0,000000	0,057971	0,000000	0,000000
5550	3408	ok		45	1,866667	3,155556	2,400000	2,466667	0,022222	2,955556	32,133333
5550	6011	alert		0								

you say id 6011 is not “examined” but it is

the period difference, of course, is zero, and if you divide by zero, you get NULL, and navicat is probably just not showing you the nulls

also, stop and think for a moment about this –

AND m.max_date >= t.myDate

since the max_date is constructed by using MAX(mydate), you can reasonably assume that the max date will ~always~ be equal to or greater than any of the dates it was constructed from

so you can drop that line :slight_smile:

If you do not consider MAX(mydate) how to search backward same code_a in the table ?
I am ready to follow your reasoning… thanks…

i’m sorry, i have no idea why you need to “search backward”

you must remember that only you know your data well enough to design the application

I don’t know if I was wrong to use MAX(mydate)… and if there are other solutions
My data I have sent…

Hi Sir Rudy:

I try this query update when changed the condition:

AND (m.max_date > t.myDate)

With:

AND (m.max_date >= t.myDate)

and the output is correct:

CODE_A	id	status
5550	2309	ok
5550	3408	ok
5550	6011	ok

Affected rows: 1
Time: 0.015ms

This is your suggestion? :rolleyes:

UPDATE listfiles_2011_11_25 t 
INNER JOIN 

( SELECT  
              CODE_A 
            , MAX(STATUS) max_STATUS 
            , MAX(myDate) max_date 
            , MAX(H2_PPM) max_H2_PPM 
            , MAX(CH4_PPM) max_CH4_PPM 
            , MAX(C2H6_PPM) max_C2H6_PPM 
            , MAX(C2H4_PPM) max_C2H4_PPM 
            , MAX(C2H2_PPM) max_C2H2_PPM 
            , MAX(CO_PPM) max_CO_PPM 
            , MAX(CO2_PPM) max_CO2_PPM 

FROM listfiles_2011_11_25 
GROUP BY CODE_A) m 

ON   m.CODE_A = t.CODE_A  
[COLOR="#FF0000"][B]AND (m.max_date >= t.myDate) [/B][/COLOR]

SET 
       t.STATUS = 
       IF( 
       (max_H2_PPM-t.H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 11 OR  
        
       (max_CH4_PPM-t.CH4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 10 OR  
        
       (max_C2H6_PPM-t.C2H6_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 8 OR   

       (max_C2H4_PPM-t.C2H4_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 13 OR  

       (max_C2H2_PPM-t.C2H2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 4 OR  

       (max_CO_PPM-t.CO_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 90 OR  

       (max_CO2_PPM-t.CO2_PPM)/PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date) 
     , EXTRACT(YEAR_MONTH FROM t.myDate)) > 850

     , 'ALERT', 'ok') 

WHERE t.CODE_A like '%5550%';  

no, it isn’t

but since it works, vwalah

Would be interesting to know your solution… do you not agree ? I can learn always from you many beautiful things…

sorry, i don’t have one

i’m still trying to understand the question

:cool:

ok, do not understand ?

that’s right

i do not understand why your tables are designed the way they are, and i do not know what your queries are trying to achieve

well I’ll try an explanation… hope for success.

I’m trying to compare rows in table mysql for field code_A.

From this comparison I need calculate:

  1. number of months between a first and the next record (myDate)
    e.g. : record #3408 compared from record #2309: 23 month (A);
  2. the difference with CO_PPM field
    e.g. : record #3408 compared from record #2309 : -133 (B);

and finally this algorithm:

  1. C_CO_PPM = (B)/(A)
    e.g. : C_CO_PPM = -133/23 = -5,78

If the algorithm (C_CO_PPM) it’s > of 90 I need update the field Status with ALERT else ‘ok’.

In my example I need update row with ‘OK’ in the field Status becuse the value -5,78 it’s < 90 value.