Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Forecast using sql

    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 below result:

    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? 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? Thank you

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Forecast using sql
    then why does you post contain NO SQL?

    post CREATE TABLE for existing & new tables
    post INSERT for sample test data
    post expected/desired result based upon test data
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by anacedent View Post
    >Forecast using sql
    then why does you post contain NO SQL?

    post CREATE TABLE for existing & new tables
    post INSERT for sample test data
    post expected/desired result based upon test data
    Sorry, I am new to the forum... Please find requested details and let me know if you need anything else. your help is very much appreciated.

    Thank you.


    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,NEXTDAT E,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,NEXTDAT E,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,NEXTDAT E,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
    ....
    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!
    Last edited by Ammours; 03-13-12 at 00:34.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    21:09:39 SQL> 
    Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,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,NEXTDAT E,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,NEXTDAT E,FREQUNIT) values ('BB3CONT001AB',to_date('01-NOV-04','DD-MON-RR'),6,1,'HI186AA12M',to_date('01-MAR-12','DD-MON-RR'),'MONTHS');
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('ASBEWDF006AE',to_date('04-JUL-01','DD-MON-RR'),3,43,'A1804AA1Y',to_date('01-MAY-12','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('BABWELL298AA',to_date('01-NOV-01','DD-MON-RR'),12,2,'HI387AA12M',to_date('01-JAN-13','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('BB3CONT001AB',to_date('01-NOV-04','DD-MON-RR'),6,1,'HI186AA12M',to_date('01-MAR-12','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> 
    21:09:51 SQL> desc pm
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     PMNUM						    VARCHAR2(12)
     FIRSTDATE					    DATE
     FREQUENCY					    NUMBER
     PMCOUNTER					    NUMBER
     JPNUM						    VARCHAR2(10)
     NEXTDATE					    DATE
     FREQUNIT					    VARCHAR2(8)
    
    21:11:47 SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Quote Originally Posted by anacedent View Post
    Code:
    21:09:39 SQL> 
    Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,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,NEXTDAT E,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,NEXTDAT E,FREQUNIT) values ('BB3CONT001AB',to_date('01-NOV-04','DD-MON-RR'),6,1,'HI186AA12M',to_date('01-MAR-12','DD-MON-RR'),'MONTHS');
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('ASBEWDF006AE',to_date('04-JUL-01','DD-MON-RR'),3,43,'A1804AA1Y',to_date('01-MAY-12','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('BABWELL298AA',to_date('01-NOV-01','DD-MON-RR'),12,2,'HI387AA12M',to_date('01-JAN-13','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> Insert into PM (PMNUM,FIRSTDATE,FREQUENCY,PMCOUNTER,JPNUM,NEXTDAT E,FREQUNIT) values ('BB3CONT001AB',to_date('01-NOV-04','DD-MON-RR'),6,1,'HI186AA12M',to_date('01-MAR-12','DD-MON-RR'),'MONTHS')
                                                                      *
    ERROR at line 1:
    ORA-00917: missing comma
    
    
    21:09:51 SQL> 
    21:09:51 SQL> desc pm
     Name					   Null?    Type
     ----------------------------------------- -------- ----------------------------
     PMNUM						    VARCHAR2(12)
     FIRSTDATE					    DATE
     FREQUENCY					    NUMBER
     PMCOUNTER					    NUMBER
     JPNUM						    VARCHAR2(10)
     NEXTDATE					    DATE
     FREQUNIT					    VARCHAR2(8)
    
    21:11:47 SQL>
    There was a space for the NEXTDATE.. I hope that is corrected now!
    Code:
    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');

Posting Permissions

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