SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Period Diff from Months

    Hi there, I need your appreciated help.

    My query in mysql:
    Code:
    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:
    Code:
    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:
    Code:
    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. #2
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I can not... I'm sorry the `months` output is incorrectly....

    Code:
    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:
    Code:
    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. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Evangelist Miguel61's Avatar
    Join Date
    Mar 2008
    Posts
    408
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your reply, I think solved my problem but I need your mind.

    Tbl_copy:
    Code:
    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:
    Code:
    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:
    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. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •