This content has been marked as final. Show 5 replies
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?
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.
If time is plotted against the X axis, what measure is plotted on the Y?
=============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;
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