Oracle - Sum function and flag column update-CodePudding

CREATE TABLE TABLE2_LINES (NAMES VARCHAR2(12), LINE_NO NUMBER, UNITS NUMBER(18,4), CODE VARCHAR2(4));

INSERT INTO TABLE2_LINES VALUES ('JOHN',1,1,'101');
INSERT INTO TABLE2_LINES VALUES ('JOHN',2,1,'202');
INSERT INTO TABLE2_LINES VALUES ('JOHN',3,1,'180');
INSERT INTO TABLE2_LINES VALUES ('JOHN',4,2,'300');

INSERT INTO TABLE2_LINES VALUES ('RYAN',1,2,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',2,1,'180');
INSERT INTO TABLE2_LINES VALUES ('RYAN',3,1,'500');

INSERT INTO TABLE2_LINES VALUES ('OLAN',1,1,'301');

INSERT INTO TABLE2_LINES VALUES ('TONY',1,1,'201');
COMMIT;

CREATE TABLE TABLE4_CODES (CD_NM VARCHAR2(12), B_CODE VARCHAR2(4), E_CODE VARCHAR2(4));

INSERT INTO TABLE4_CODES VALUES ('CODELIST','100','101');
INSERT INTO TABLE4_CODES VALUES ('CODELIST','180','180');
INSERT INTO TABLE4_CODES VALUES ('CODELIST','200','219');

INSERT INTO TABLE4_CODES VALUES ('EXCODELIST','180','180');
COMMIT;


CREATE TABLE RESULT1 (NAMES VARCHAR2(12), TOT_UNITS NUMBER, FLAG VARCHAR2(2));
COMMIT;

Case 1: For person JOHN, We have eligible codes i.e. 101,202,180 (these codes present in TABLE4_CODES table of CD_NM CODELIST). It has other eligible codes along with 180 so SUM(UNITS) is 3 and FLAG should be populated as O
Case 2: For person RYAN, We have only 1 eligible code i.e. 180 only (this code present in TABLE4_CODES table of CD_NM EXCODELIST...Need to look into CD_NM = EXCODELIST instead of CODELIST), so SUM(UNITS) is 3 and FLAG should be populated as N
Case 3: For person OLAN, we don't have eligible codes so need to do anything.
Case 4: For person TONY, we have eligible code 201 but we don't have 180 so (this code present in TABLE4_CODES table of CD_NM = CODELIST), so SUM(UNITS) is 1 and FLAG should be populated as N

If particular person has multiple eligible codes along with 180, we need to look into TABLE4_CODES table of CD_NM CODELIST before proceeding for sum calculation. Ex: JOHN
If particular person has single/multiple eligible codes with don't have 180, we need to look into TABLE4_CODES table of CD_NM CODELIST before proceeding for sum calculation Ex: TONY
If particular person has only 1 eligible code i.e. 180, we need to look into TABLE4_CODES table of CD_NM EXCODELIST before proceeding for sum calculation. Ex: RYAN

Output to be expected in RESULT1 table using TABLE2_LINES and TABLE4_CODES:

NAMES    TOT_UNITS       FLAG
-----------------------------
JOHN      3               O
RYAN      3               N 
TONY      1               O

CodePudding user response：

This is how I understood it. Read comments within code.

SQL> with
  2  t180 as
  3    -- does NAME have code = 180? If so, return 1; else, return 0
  4    (select t2.names,
  5       max(case when t2.code = 180 then 1 else 0 end) as f180
  6     from table2_lines t2
  7     group by t2.names
  8    ),
  9  eligible as
 10    -- number of eligible rows per NAME
 11    (select t2.names,
 12       count(distinct t2.code) cnt_eligible
 13     from table2_lines t2 join table4_codes t4 on t2.code
 14          between t4.b_code and t4.e_code
 15     group by t2.names
 16    ),
 17  total_rows as
 18    -- gotal number of rows per NAME
 19    (select t2.names,
 20       count(*) cnt_total
 21     from table2_lines t2
 22     group by t2.names
 23    )
 24  -- apply rules (within the CASE expression) to return desired result
 25  select distinct
 26    t2.names,
 27    case when e.cnt_eligible = 1  and f.f180 = 1 then t.cnt_total -- Ryan, N
 28         when e.cnt_eligible >= 1 and f.f180 = 0 then e.cnt_eligible -- Tony, O
 29         when e.cnt_eligible >= 1 and f.f180 = 1 then e.cnt_eligible -- John, O
 30    end tot_units,
 31    --
 32    case when e.cnt_eligible = 1  and f.f180 = 1 then 'N' -- Ryan, N
 33         when e.cnt_eligible >= 1 and f.f180 = 0 then 'O' -- Tony, O
 34         when e.cnt_eligible >= 1 and f.f180 = 1 then 'O' -- John, O
 35    end flag
 36  from table2_lines t2 join t180 f on f.names = t2.names
 37  join eligible e on e.names = t2.names
 38  join total_rows t on t.names = t2.names;

Result:

NAMES         TOT_UNITS FLAG
------------ ---------- ----
JOHN                  3 O
RYAN                  3 N
TONY                  1 O

SQL>