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

1. Registered User
Join Date
May 2002
Posts
34

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

Hello PL/SQL gurus!

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.

Pit.

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
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.

3. Registered User
Join Date
May 2002
Posts
34

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

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. Registered User
Join Date
Jun 2003
Location
West Palm Beach, FL
Posts
2,713
Show us the money...(your code) and indicate where you are stuck.

5. Registered User
Join Date
May 2002
Posts
34

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

Here is my code:

TYPE v_array IS TABLE OF OBSERVATIONDATA%ROWTYPE
INDEX BY BINARY_INTEGER;
QUOTECOUNTS V_ARRAY;
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
AL_KPI := 0;

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

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

-- AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;

AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;

AL_KPI_TMP := QUOTECOUNTS(I-1).QUOTE_CNT;
end if;

END IF;

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

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

/

It compiles but does not do what I expect.

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

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.

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