Dear Experts,
I am seeking you help on how a query can be written in sql using olap, connect by or any other function to achieve best performance (I know how I can do it in PL/SQL but I again want it to be one query and with the highest performance):
Preventive maintenance forecast:
Input: current counter, frequency, frequency units (weeks, months, years), last generated date. There is also a linked table with it showing job plan sequences where next job plan to run= the highest sequence job plan sequence evenly dividing the counter + 1 (e.g. 1,6,12 where 1 st month job plan seq 1 is due, 2 nd month, seq 1, ... 6 th month is jobplan 6 and 12 th month is for job plan 12)
The required output is:
Dates due from last generated till the date specified as parameter. That means, result should show all future dates when pm is due. Also, the right job plan should show with the due date and the counter on that date.
Is there a way to write a model for that (if that is the best)? What would it look like?
How can we show multiple measures in that case (date, job plan and counter), what is the partition? What are mydimensions?
CREATE TABLE "MAXIMO"."PM"
( "PMNUM" VARCHAR2(12 BYTE),
"FIRSTDATE" DATE,
"FREQUENCY" NUMBER,
"PMCOUNTER" NUMBER,
"JPNUM" VARCHAR2(10 BYTE),
"NEXTDATE" DATE,
"FREQUNIT" VARCHAR2(8 BYTE)
)
CREATE TABLE "MAXIMO"."PMSEQUENCE"
( "PMNUM" VARCHAR2(12 BYTE),
"JPNUM" VARCHAR2(10 BYTE),
"INTERVAL" NUMBER
)
Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDATE,FREQUNIT) values ('ASBEWDF006AE',to_date('04-JUL-01','DD-MON-RR'),3,43,'A1804AA1Y',to_date('01-MAY-12','DD-MON-RR'),'MONTHS');
Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDATE,FREQUNIT) values ('BABWELL298AA',to_date('01-NOV-01','DD-MON-RR'),12,2,'HI387AA12M',to_date('01-JAN-13','DD-MON-RR'),'MONTHS');
Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDATE,FREQUNIT) values ('BB3CONT001AB',to_date('01-NOV-04','DD-MON-RR'),6,1,'HI186AA12M',to_date('01-MAR-12','DD-MON-RR'),'MONTHS');
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('ASBEWDF006AE','A1804AA3M',1);
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('ASBEWDF006AE','A1804AA1Y',4);
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('BB3CONT001AB','HI186AA06M',1);
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('BB3CONT001AB','HI186AA12M',2);
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('BABWELL298AA','HI387AA12M',1);
Insert into PMSEQUENCE (PMNUM,JPNUM,INTERVAL) values ('BABWELL298AA','HI387AA24M',2);
Expected Results:
PM ITERATION DATE JPNUM COUNTER
ASBEWDF006AE 1 1 MAY 2012 A1804AA1Y 44
ASBEWDF006AE 2 1 AUG 2012 A1804AA3M 45
ASBEWDF006AE 3 1 NOV 2012 A1804AA3M 46
ASBEWDF006AE 4 1 FEB 2013 A1804AA3M 47
ASBEWDF006AE 5 1 MAY 2013 A1804AA1Y 48
....
BB3CONT001AB 1 01-MAR-12 HI387AA12M 2
BB3CONT001AB 2 01-SEP-12 HI186AA06M 3
BB3CONT001AB 3 01-MAR-13 HI387AA12M 4
..............
Till Iteration X given as parameter for all PMs in the table. The aim is to have maximum performance for a huge table forecasting over 10,20 and even 30 years... using OLAP or best available oracle 11g tech.
etc.
Thanks again!
Edited by: user11078797 on Mar 12, 2012 10:07 PM
Edited by: user11078797 on Mar 12, 2012 10:08 PM