Period Diff from Months

Databases
1

Hi there, I need your appreciated help.

My query in mysql:


SELECT 
       ID
     , CODE
     , C2H4_PPM 
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM DATES)
     , EXTRACT(YEAR_MONTH FROM DATES)) MONTHS
     , INCREMENT
FROM Tbl_copy
WHERE 1 
AND CODE = '900194'
ORDER BY ID DESC;

And my partial incorrect output:


ID	CODE	DATES		C2H4_PPM	MONTHS	INCREMENT
7608	900194	2011-04-08	4,00		0
6145	900194	2009-10-01	2,00		0		
821	900194	2003-11-05	6,00		0

I need this output:


ID	CODE	DATES		C2H4_PPM	MONTHS	INCREMENT
7608	900194	2011-04-08	4,00		18	 0,111
6145	900194	2009-10-01	2,00		71	-0,056				
821	900194	2003-11-05	6,00

Months is: Period Diff between 2009-10-01 and 2003-11-05 ( ID #6145 and ID #821 );
Months is: Period Diff between 2011-04-08 and 2009-10-01 ( ID #7608 and ID #6145 );

Increment C2H4_PPM is calculate with: (2,00-6,00)/71 = -0,056 ( ID #6145 and ID #821 );
Increment C2H4_PPM is calculate with: (4,00-2,00)/18 = 0,111 ( ID #7608 and ID #6145 );

Can you help me?
Thanks in advance.

2

I can not… I’m sorry the months output is incorrectly… :frowning:

SELECT        
       ID
     , t.CODE
     , DATES
     , COUNT(*)-1 NM
     , C2H4_PPM
     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.DATES)) MONTHS
FROM ( 
       SELECT CODE
     , MAX(DATES) max_date
FROM Tbl_copy
GROUP BY CODE, DATES) m
       INNER JOIN Tbl_copy t ON t.CODE = m.CODE
       WHERE 1 AND t.CODE > 0
       GROUP BY t.CODE, DATES;

Wrong output:

ID	CODE		DATES		NM	C2H4_PPM	MONTHS
821	900194		2003-05-11	2	6		0
6145	900194		2009-10-01	2	2		77
7608	900194		2011-04-08	2	4		95

The problem is PERIOD_DIFF of ID #7608 and ID #6145… 18 months and not 95 months…

Can you help me?
Thanks in advance

3

Well for starters
, PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM DATES)
, EXTRACT(YEAR_MONTH FROM DATES)) MONTHS
is gonna give you 0 because you’re comparing the exact same thing. (YEAR_MONTH FROM DATES in both clauses).

Now the second one.
What seems to be your problem is that your MAX isnt giving you the max (Because your months column should read 95,18,0 (in order). Your ‘max date’ is 2011-04-08.)

GROUP BY CODE, DATES) m

Should that not just be GROUP BY CODE ?

4

Thanks for your reply, I think solved my problem but I need your mind.

Tbl_copy:

ID	CODE	DATES		C2H4_PPM
7608	900194	2011-04-08	4
6145	900194	2009-10-01	2
821	900194	2003-05-11	6

SQL Query:

SELECT        
       t.ID
     , t.CODE

     , m.max_date
     , t.DATES

     , m.max_C2H4_PPM
     , t.C2H4_PPM

     , PERIOD_DIFF(EXTRACT(YEAR_MONTH FROM m.max_date)
     , EXTRACT(YEAR_MONTH FROM t.DATES)) MONTHS

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

FROM ( 
       SELECT 
       CODE
     , MAX(DATES) max_date
     , MAX(C2H4_PPM) max_C2H4_PPM

FROM Tbl_copy
       GROUP BY CODE, DATES) m

       INNER JOIN Tbl_copy t ON t.CODE = m.CODE AND (m.max_date > t.DATES)
       WHERE 1 AND t.CODE > 0

       GROUP BY t.CODE, DATES
       ORDER BY t.CODE, DATES DESC;

Output (for me is correct, but… )

CODE	max_date	DATES		MONTHS	max_C2H4_PPM	C2H4_PPM	INCREMENT
900194	2011-04-08	2009-10-01	18	4		2		0.111
900194	2009-10-01	2003-05-11	77	2		6		-0.052
  • The PERIOD_DIFF for months it’s ok;
  • The formula for increase (increment) it’s ok.

You have comments or suggestions?
Thanks.

5

Yeah, this doesnt make sense to me at all. How do you have multiple Max dates for a single code? I mean… if your code does what you want it to do, then good for you! I dont personally get it, but i’m not aware of the situation you have.