Dear All,
Myself has the below 3 tables.
Request you to share the query to display the status for each department as either P or A or C.
P-Pending A-Accepted C-Completed
Prirority of status
C as top priority, A as second priority, P as least priority
The rules are as follows.
- If a department having more than one program and all the program having the equal status, then the flag column should return C
- If a department having more than one program and all the program does not having the equal status, then the flag should return A (second priority) or P(least priority)
- If a department having only one program then the flag column should give the priority for C as top priority, A as second priority, P as least priority and the flag column should return C (if the program has all the 3 status, if it has only P and A then it should return as A)
The table script and insert script are as follows
CREATE TABLE TABLE1 (Table1Id NUMBER, Deptid NUMBER);
INSERT INTO TABLE1 VALUES (1,101);
COMMIT;
CREATE TABLE TABLE2 (Table2Id NUMBER, Deptid NUMBER,ProgramID NUMBER);
INSERT INTO TABLE2 VALUES (1,101,1000);
INSERT INTO TABLE2 VALUES (1,101,1001);
INSERT INTO TABLE2 VALUES (1,101,1002);
COMMIT;
CREATE TABLE TABLE3 (Table3Id NUMBER,ProgramID NUMBER, Status VARCHAR2(10));
INSERT INTO TABLE3 VALUES (1,1000,'P');
INSERT INTO TABLE3 VALUES (1,1000,'A');
INSERT INTO TABLE3 VALUES (1,1000,'C');
INSERT INTO TABLE3 VALUES (1,1001,'P');
INSERT INTO TABLE3 VALUES (1,1001,'A');
INSERT INTO TABLE3 VALUES (1,1001,'C');
INSERT INTO TABLE3 VALUES (1,1002,'P');
INSERT INTO TABLE3 VALUES (1,1002,'A');
COMMIT;
