This Question extends from Oracle SQL Where Conditions weight but, is more general (without product restriction, but give one by product value).
I have a table
DROP TABLE mytable;
CREATE TABLE mytable
(
product_code VARCHAR2(20 BYTE) NOT NULL ENABLE,
priority NUMBER NOT NULL ENABLE,
date_act DATE,
date_dis DATE
);
Populate the table
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('bla', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'), TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '2', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('bla', '3', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '4', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('bla', '5', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('bla', '6');
INSERT INTO mytable (product_code, priority) VALUES ('bla', '7');
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('foo', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('foo', '1', TO_DATE('2019-02-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('foo', '2', TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('foo', '3');
INSERT INTO mytable (product_code, priority, date_act, date_dis) VALUES ('tmp', '0', TO_DATE('2019-01-01', 'YYYY-MM-DD'), TO_DATE('2019-01-31', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_act) VALUES ('tmp', '1', TO_DATE('2019-01-01', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority, date_dis) VALUES ('tmp', '2', TO_DATE('2019-02-28', 'YYYY-MM-DD'));
INSERT INTO mytable (product_code, priority) VALUES ('tmp', '3');
The content
SELECT * FROM mytable;
And Output
PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS
-------------------- ---------- --------- ---------
bla 0 01-JAN-19 31-JAN-19
bla 1 01-FEB-19 28-FEB-19
bla 2 01-JAN-19
bla 3 01-FEB-19
bla 4 31-JAN-19
bla 5 28-FEB-19
bla 6
bla 7
foo 0 01-JAN-19 31-JAN-19
foo 1 01-FEB-19
foo 2 31-JAN-19
foo 3
tmp 0 01-JAN-19 31-JAN-19
tmp 1 01-JAN-19
tmp 2 28-FEB-19
tmp 3
Condition definitions
date_act and date_dis defined.date_dis defined but date_act not.date_act defined but date_dis not.date_act and date_dis not defined.If condition_weight 4 is true the left (3, 2 and 1) will be ignored.
If condition_weight 4 is false and condition_weight 3 is true , condition_weight 2 and condition_weight 1 will be ignored.
If condition_weight 4 and 3 are false and condition_weight 2 is true condition_weight 1 will be ignored.
If condition_weight 4, 3 and 2 are false only condition_weight 1 will be evaluated.
THE QUESTION: How obtain only one row by each product_code with its priority highest, with input parameter date_submit with condition weight definitions?
EXAMPLE of OUTPUT Required NOTE: The below table results are created manually (I need similar results, but I don't have the query).
DATE_SUBMIT :='2019/01/15'
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 0 bla 0 01-JAN-19 31-JAN-19 2019/01/15
4 0 foo 0 01-JAN-19 31-JAN-19 2019/01/15
4 0 tmp 0 01-JAN-19 31-JAN-19 2019/01/15
DATE_SUBMIT :='2019/02/15'
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
4 1 bla 1 01-FEB-19 28-FEB-19 2019/02/15
2 1 foo 1 01-FEB-19 2019/02/15
3 2 tmp 2 28-FEB-19 2019/02/15
DATE_SUBMIT :='2018/12/31'
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
3 5 bla 5 28-FEB-19 2018/12/31
3 2 foo 2 31-JAN-19 2018/12/31
3 2 tmp 2 28-FEB-19 2018/12/31
DATE_SUBMIT :='2019/12/31'
CONDITION_WEIGHT PRIO PRODUCT_CODE PRIORITY DATE_ACT DATE_DIS :DATE_SUBMIT
---------------- ---------- -------------------- ---------- --------- --------- --------------------------------------------------------------------------------------------------------------------------------
2 3 bla 3 01-FEB-19 2019/12/31
2 1 foo 1 01-FEB-19 2019/12/31
2 1 tmp 1 01-JAN-19 2019/12/31
Check the answer https://stackoverflow.com/a/59779653/1410223 result, but if is it possible more simpler Query.
EDIT Only priority with highest priority must be .
Assign condition weights using case when. Then take best row for each product, use rank():
with
d as (select date '2019-02-15' date_submit from dual),
t as (
select m.*, case
when date_act is not null and date_dis is not null then 4
when date_act is null and date_dis is not null then 3
when date_act is not null and date_dis is null then 2
else 1 end condition
from mytable m)
select product_code, priority, date_act, date_dis, condition, date_submit
from (
select t.*, d.*,
rank() over ( partition by product_code order by condition desc ) rnk
from t join d
on date_submit between nvl(date_act, date_submit) and nvl(date_dis, date_submit))
where rnk = 1