Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    34

    Unanswered: PL/SQL procedure to compute ratios across variable number of months

    Hello PL/SQL gurus!
    Please help me with the problem below:

    I have a table below:
    DeptID MONTH Count
    19 2010-04 1392
    19 2010-05 1134
    19 2010-06 1094
    19 2010-07 1333
    29 2010-04 2217
    29 2010-05 2324
    29 2010-06 2494
    29 2010-07 2912
    32 2010-04 1782
    32 2010-05 1733
    32 2010-06 1995
    32 2010-07 2318


    Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.
    If ratio >= threshold N number of times I need to make a note of that event.
    Threshold = 0.1
    N = 2 - alert needs to exceed threshold two consequtive times

    Here is data processing algorithm:
    1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
    2. check change value against threshold: 0.18 > 0.1
    3. Threshold was exceeded, set alert_fired_cnt counter to = 1
    4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
    5. check change value against threshold: 0.21 > 0.1
    6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
    7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
    8. We're in montn 2010-07: abs(1333/1294-1)=0.03
    8. check change value against threshold: 0.03 < 0.1
    9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0

    Above algorithm needs to be run for all DeptID groups.

    I load above data into an associative array and loop through elements.
    I am having trouble keeping computations within each DeptID group.

    Please help!!!
    Thanks a lot in advance,
    Pit.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    you'll get more/better/faster replies if you register & post to URL below
    OraFAQ Forum: SQL & PL/SQL
    but you need to include CREATE TABLE & INSERT statements to make it easier to assist.
    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
    May 2002
    Posts
    34

    PL/SQL procedure to compute ratios across variable number of months

    Sorry about that. Please see DDL and DML below:

    DROP TABLE OBSERVATIONDATA;
    CREATE TABLE OBSERVATIONDATA
    (
    "DEPTID" NUMBER,
    "MONTH_LABEL_YYYY_MM" NVARCHAR2(10),
    "MONTH_RNK" NUMBER, -- month rank within deptid group
    "QUOTE_CNT" NUMBER
    )
    ;

    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-04', 1, 1392);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-05', 2, 1134);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-06', 3, 1294);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-07', 4, 1333);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-08', 5, 739);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-09', 6, 719);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-10', 7, 957);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-11', 8, 795);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2010-12', 9, 992);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-01', 10, 1099);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-02', 11, 780);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-03', 12, 892);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-04', 13, 885);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-05', 14, 755);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-06', 15, 729);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-07', 16, 1100);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-08', 17, 224);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-09', 18, 263);
    INSERT INTO OBSERVATIONDATA VALUES (19, '2011-10', 19, 5);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-04', 1, 2217);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-05', 2, 2324);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-06', 3, 2494);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-07', 4, 2912);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-08', 5, 1536);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-09', 6, 1821);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-10', 7, 2609);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-11', 8, 1834);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2010-12', 9, 2295);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-01', 10, 2745);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-02', 11, 1909);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-03', 12, 1938);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-04', 13, 2295);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-05', 14, 2006);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-06', 15, 1975);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-07', 16, 2900);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-08', 17, 1056);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-09', 18, 1470);
    INSERT INTO OBSERVATIONDATA VALUES (29, '2011-10', 19, 10);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-04', 1, 1782);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-05', 2, 1733);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-06', 3, 1995);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-07', 4, 2318);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-08', 5, 1489);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-09', 6, 1257);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-10', 7, 1671);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-11', 8, 1363);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2010-12', 9, 2036);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-01', 10, 2047);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-02', 11, 1412);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-03', 12, 1487);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-04', 13, 1648);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-05', 14, 1407);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-06', 15, 1802);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-07', 16, 2029);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-08', 17, 854);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-09', 18, 1336);
    INSERT INTO OBSERVATIONDATA VALUES (32, '2011-10', 19, 17);

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Show us the money...(your code) and indicate where you are stuck.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    May 2002
    Posts
    34

    PL/SQL procedure to compute ratios across variable number of months

    Here is my code:

    CREATE OR REPLACE PROCEDURE ALERT_DURATION_V2(P_ALERT_DURATION NUMBER, P_ALERT_THRESHOLD NUMBER) IS

    TYPE v_array IS TABLE OF OBSERVATIONDATA%ROWTYPE
    INDEX BY BINARY_INTEGER;
    QUOTECOUNTS V_ARRAY;
    V_ALERT_FIRED_CNT NUMBER :=0;
    V_ALERT_TRIGGERED NUMBER :=0;
    AL_KPI number :=0;
    AL_KPI_TMP NUMBER := 0;
    AL_KPI_TMP_BASE number := 0;

    BEGIN
    -- Insert data into associative array
    SELECT DeptID, MONTH_LABEL_YYYY_MM, MONTH_RNK, QUOTE_CNT BULK COLLECT
    INTO QUOTECOUNTS
    FROM OBSERVATIONDATA
    ORDER BY DeptID, MONTH_RNK;

    -- Step through array for alert evaluation
    FOR I IN 1..QUOTECOUNTS.LAST LOOP

    IF QUOTECOUNTS.PRIOR(I) IS NULL THEN
    V_ALERT_FIRED_CNT := 0;
    AL_KPI := 0;

    ELSIF QUOTECOUNTS(I).DeptID != QUOTECOUNTS(I-1).DeptID THEN

    V_ALERT_FIRED_CNT := 0;
    AL_KPI := 0;
    DBMS_OUTPUT.PUT_LINE('New DeptID = '||QUOTECOUNTS(I).DeptID);
    DBMS_OUTPUT.PUT_LINE('DeptID has changed');
    ELSIF QUOTECOUNTS(I).DeptID = QUOTECOUNTS(I-1).DeptID THEN

    AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;
    AL_KPI := ROUND(((QUOTECOUNTS(I).QUOTE_CNT / AL_KPI_TMP) - 1), 4);

    if ABS(AL_KPI) < P_ALERT_THRESHOLD THEN
    -- AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;
    v_alert_fired_cnt := 0;

    elsif (ABS(AL_KPI) >= p_alert_threshold and v_alert_fired_cnt = 0) then
    V_ALERT_FIRED_CNT := V_ALERT_FIRED_CNT + 1;
    AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;

    elsif abs(al_kpi) >= p_alert_threshold and (v_alert_fired_cnt != 0 and v_alert_fired_cnt < p_alert_duration) then
    V_ALERT_FIRED_CNT := V_ALERT_FIRED_CNT + 1;
    AL_KPI_TMP := QUOTECOUNTS(I- P_ALERT_DURATION).QUOTE_CNT;
    elsif al_kpi >= p_alert_threshold and v_alert_fired_cnt = p_alert_duration then
    V_ALERT_FIRED_CNT := 1;
    AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;
    end if;

    END IF;

    --AL_KPI := ROUND(((QUOTECOUNTS(I).QUOTE_CNT / AL_KPI_TMP) - 1), 4);
    DBMS_OUTPUT.PUT_LINE(I||' '||QUOTECOUNTS(I).DeptID||' '||QUOTECOUNTS(I).MONTH_LABEL_YYYY_MM||' '||QUOTECOUNTS(I).QUOTE_CNT||' '||AL_KPI||' v_alert_fired_cnt= '||v_alert_fired_cnt);

    END LOOP; -- end of QUOTECOUNTS array

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('End of array');
    WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20001,'Error encountered - '||SQLCODE||' - '||SQLERRM);

    END ALERT_DURATION_V2;
    /

    It compiles but does not do what I expect.

    Here is what I expect this is data for DeptID = 19

    MONTH QUOTE_CNT RATIO ALERT_FIRED_CNT
    2010-04 1392
    2010-05 1134 0.185344828 1
    2010-06 1294 0.070402299 0
    2010-07 1333 -0.030139104 0
    2010-08 739 0.445611403 1
    2010-09 719 0.460615154 2
    2010-10 957 0.282070518 3
    2010-11 795 0.169278997 1
    2010-12 992 -0.036572623 0
    2011-01 1099 -0.107862903 1
    2011-02 780 0.213709677 2
    2011-03 892 0.100806452 3
    2011-04 885 0.007847534 0
    2011-05 755 0.146892655 1
    2011-06 729 0.176271186 2
    2011-07 1100 -0.242937853 3
    2011-08 224 0.796363636 1
    2011-09 263 0.760909091 2
    2011-10 5 0.995454545 3

    I need to use as denominator value of QUOTE_CNT from denominator in the month when alert fired first time. It becomes my base for computations.
    That's what I can't figure out how to do - I can't hold the value of array element since I am in the loop and values get reset.
    Also, v_alert_fired_cnt should restart right after v_alert_fired_cnt = p_alert_duration. My code is inconsistent about it too...

    Thank you,
    Pit
    Last edited by PeterS; 11-22-11 at 21:09. Reason: typo

Tags for this Thread

Posting Permissions

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