Oracle SQL Choosing between multiple rows with the same date

Question

Basically what I'm trying to do is assign an owner based on their availability on a specific date. Now I got it assigned correctly, but as soon as the owner is appointed, I do not want him to look for others, since he is already assigned. The pseudo code will be like this:

if person1 is available today then assign person1
else if person1 is NOT available today then assign person2
else if person 2 is NOT available today then assign person3
else assign it to N/A

It just matches both tables and sees if the person is available today to match the person with the task. However, as I mentioned earlier, he makes 3 lines for each task, this is how the results look

Status = 1 Available Status = 2 Not Available

+----------------+----------+-------+--------+--------+--------+--------+
| ASSIGNED_OWNER | THE_DATE | STATE | TASKID | OWNER1 | OWNER2 | OWNER3 |
+----------------+----------+-------+--------+--------+--------+--------+
| john           | 3/2/2018 |     2 |   1234 | john   | bob    | rick   |
| N/A            | 3/2/2018 |     2 |   1234 | john   | bob    | rick   |
| bob            | 3/2/2018 |     1 |   1234 | john   | bob    | rick   |
+----------------+----------+-------+--------+--------+--------+--------+

This is how it should look like once it assigns an owner, then just do not make other entries for this taskid

+----------------+----------+-------+--------+--------+--------+--------+
| ASSIGNED_OWNER | THE_DATE | STATE | TASKID | OWNER1 | OWNER2 | OWNER3 |
+----------------+----------+-------+--------+--------+--------+--------+
| bob            | 3/2/2018 |     1 |   1234 | john   | bob    | rick   |
+----------------+----------+-------+--------+--------+--------+--------+

. ? , , ? ? . 3,

, , . , , . PL/SQL - ?

, ,

, ownerdata​​p >

+--------+--------+--------+--------+
| TASKID | OWNER1 | OWNER2 | OWNER3 |
+--------+--------+--------+--------+
|  12345 | john   | bob    | rick   |
+--------+--------+--------+--------+

+----------+-------------+----------+
| PERSONID | AVAIL_STATE | THE_DATE |
+----------+-------------+----------+
| john     |     2       | 3/2/2018 |
| bob      |     1       | 3/2/2018 |
| rick     |     1       | 3/2/2018 |
+----------+-------------+----------+

Answer 1

. , WITH ( , ). WITH . - ( WITH, ).

. - . , . U ( "" ) , , , , "", . C ( "calendar" ) , . U C "owner" - , , . , .

OWNERDATA , TASKID. (1, 2 3) FIRST ( - , , ), NVL, ASSIGNED_OWNER 'N/A', .

with ownerdata ( TASKID, OWNER1, OWNER2, OWNER3 ) as (
    select 12345, 'john', 'bob', 'rick' from dual
  ),
  calendar ( PERSONID, aVAIL_STATE, THE_DATE ) as (
    select 'john', 2, to_date('3/2/2018', 'mm/dd/yyyy') from dual union all
    select 'bob' , 1, to_date('3/2/2018', 'mm/dd/yyyy') from dual union all
    select 'rick', 1, to_date('3/2/2018', 'mm/dd/yyyy') from dual
  ),
  input_date ( dt ) as (
    select to_date('3/2/2018', 'mm/dd/yyyy') from dual
  )
select   nvl( min(j.owner) keep (dense_rank first order by pref), 'N/A') as assigned_owner,
         (select dt from input_date) as the_date,
         o.taskid, o.owner1, o.owner2, o.owner3
from     ownerdata o
         left outer join
         (
           select taskid, owner, pref
           from     (
                      select  taskid, owner, pref
                      from    ownerdata
                      unpivot ( owner for pref in (owner1 as 1, owner2 as 2, owner3 as 3) )
                    ) u
                  inner join 
                    ( select  personid 
                      from    calendar
                      where   avail_state = 1 and the_date = (select dt from input_date)
                    ) c
                  on u.owner = c.personid
         ) j
         on o.taskid = j.taskid
group by o.taskid, o.owner1, o.owner2, o.owner3
;

ASSIGNED_OWNER THE_DATE     TASKID OWNER1 OWNER2 OWNER3
-------------- -------- ---------- ------ ------ ------
bob            3/2/2018      12345 john   bob    rick