This discussion is archived
5 Replies Latest reply: Feb 15, 2013 7:15 AM by bostonmacosx RSS

Line Chart query...having issues with finding the right SQL...tough query

bostonmacosx Newbie
Currently Being Moderated
This initaial entry was much longer and confusing.
I'm looking to make ar running total based using time periods...see the link in the last post to get to the demo.apex.com area.

Rob

Edited by: bostonmacosx on Feb 14, 2013 1:33 PM
  • 1. Re: Line Chart query...having issues with finding the right SQL...tough query
    Howard (... in Training) Pro
    Currently Being Moderated
    Off the top of my head, when I want to "group by" month, I truncate the "date" in question to the first of the month and then "group by" that. I don't think that fits your problem.

    Suppose you have had a machine since CREATE_DT = '10/15/2012', then is what you want is to count it in Oct 2012, Nov 2012, Dec 2012, Jan 2013, and Feb 2013? And if a sister machine, started the same day, broke on 01/03/13, then you want the same counts except nothing for Febuary?

    Is that right? I wonder if there an Oracle "analytic" function that does this type of computation?

    Regards,
    Howard
  • 2. Re: Line Chart query...having issues with finding the right SQL...tough query
    fac586 Guru
    Currently Being Moderated
    bostonmacosx wrote:
    Hello there. So I hope I can explain this sufficiently:
    OUT OF THE WAY: 11g 4.1.1

    I'm going to simplify my data so that it is clear what I'm looking to do.

    I want to have a line chart grouped by date. Let's say monthly...this is easy to do if you are dealing with one specific date and some value you can build the series against with case statements. I've done that a million times.

    The columns of data I'm dealing with are as follows:

    CREATE_DT(create date)
    RETIRE_DT(retire date)
    MACHINE_TYPE(type of machine)

    So lets say I want to see a line chart where each line(data point) is a MACHINE_TYPE and each bin is a month. That month should be any machine with a CREATE_DT below then end of the month and a RETIRE_DT which is either Greater then the end of the month or is NULL(ala hasn't been retired yet).

    In the query for a chart which is
    SELECT LINK,LABEL,CASE()"",CASE()"",CASE()"" from TABLE GROUP BY ROLLUP(VALUE)
    I guess I'm not seeing how to be able to put these values together so that it walks month by month and figures out the values and puts them in the correct "bin" of time along the X axis of the chart.

    I hope I'm being semi clear as it is hard to explain this scenario.
    Nope. Clear as mud.

    If time is plotted against the X axis, what measure is plotted on the Y?
    =============
    on another note I built this table from the data:
    01-JAN-12     01-FEB-12     01-MAR-12     01-APR-12     01-MAY-12     01-JUN-12     01-JUL-12     01-AUG-12     01-SEP-12     01-OCT-12     01-NOV-12     01-DEC-12     01-JAN-13     ENVOS
    59     59     59     59     59     59     59     59     59     59     59     60     60      Alias
    12     26     26     26     26     26     26     26     26     26     26     26     26      Blade
    9     9     9     9     9     9     9     9     9     9     9     9     9      DataMvr
    Using the function:
    create or replace FUNCTION ACTIVE_SYSTEMS
    RETURN VARCHAR2
    is
    var1 VARCHAR2(4000):= '';
    start_date DATE:= to_Date('05-JAN-2012','DD-MON-YYYY');
    end_date DATE:= to_Date('08-JAN-2013','DD-MON-YYYY');
    new_start_date DATE;
    
    BEGIN
    new_start_date:=trunc(start_date,'MONTH');
    var1 := q'!SELECT !';
    while(new_start_date<end_date)
    LOOP
    var1 := var1 || q'! count(case when create_dt<'!'||to_char(new_start_date,'DD-MON-YY')||q'!' and (retire_dt IS NULL or retire_dt>'!'||to_char(new_start_date,'DD-MON-YY')||q'!') then 1 end) "!' ||to_char(new_start_date,'DD-MON-YY')||q'!",!';
    new_start_date:= add_months(new_start_date,1);
    END LOOP;
    var1 := var1 || q'! CMS_NODE_OS.OS_TYPE||' '||node_env as envos from CMS.CMS_NODE LEFT join CMS.CMS_NODE_OS on CMS.CMS_NODE.NODE_NAME=CMS.CMS_NODE_OS.NODE_NAME where retire_dt is NULL group by rollup(CMS_NODE_OS.OS_TYPE||' '||node_env)!';
    RETURN var1;
    END;
    Instead of inadequate attempts to explain this here with fragments of code that we can't do anything with because we don't possess the objects and data they're based on, show us something. Create the objects and some sample data in a workspace on apex.oracle.com and post guest developer credentials. Sketch the required chart or mock it up in a spreadsheet and upload it as an image or PDF so we can see what you're aiming for.
  • 3. Re: Line Chart query...having issues with finding the right SQL...tough query
    bostonmacosx Newbie
    Currently Being Moderated
    Here is the link which I hope makes it more clear:

    http://apex.oracle.com/pls/apex/f?p=65176:LOGIN_DESKTOP:2879055333849

    the UN and PW are demo/demo

    Thanks
    Rob
  • 4. Re: Line Chart query...having issues with finding the right SQL...tough query
    Kenny Hanberg Explorer
    Currently Being Moderated
    Hi Rob

    One way to achive Your goal is create a timeline and left join Your result like in my example...
    SELECT NULL LINK, 
           TO_CHAR(MONTHS_,'YYYY-MM') DATIME, 
           SUM(DECODE(OS_TYPE,'AIX','1',0)) "AIX", 
           SUM(DECODE(OS_TYPE,'Linux','1',0)) "Linux" , 
           SUM(DECODE(OS_TYPE,'Windows','1',0)) "Windows" 
      FROM
      ( SELECT ADD_MONTHS(TRUNC(SYSDATE,'MM'),-ROWNUM + 1) MONTHS_
          FROM DUAL CONNECT BY LEVEL <= (SELECT CEIL( MONTHS_BETWEEN(SYSDATE,TO_DATE('2005-01-01','YYYY-MM-DD'))) FROM DUAL) 
      ) THE_TIMELINE
      LEFT JOIN MACHINES ON ( MONTHS_ BETWEEN CREATE_DT AND LAST_DAY(NVL(RETIRE_DT,MONTHS_)) )
     GROUP BY (TO_CHAR(MONTHS_,'YYYY-MM')) ORDER BY DATIME
    /Kenny
  • 5. Re: Line Chart query...having issues with finding the right SQL...tough query
    bostonmacosx Newbie
    Currently Being Moderated
    This appears to work although I don't understand what is going on. I'm trying to educate myself LEVEL and the creation of a timeline although there appears not to be much out there about this. We'll see what I can do I hate using solutions without understanding them but thanks again Kenny

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points