bostonmacosx wrote:Nope. Clear as mud.
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:
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
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.
SELECT LINK,LABEL,CASE()"",CASE()"",CASE()"" from TABLE GROUP BY ROLLUP(VALUE)
I hope I'm being semi clear as it is hard to explain this scenario.
=============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.
on another note I built this table from the data:Using the function:
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
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;
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