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.

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.

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);

Show us the money...(your code) and indicate where you are stuck.

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