Conditional Analytic Function

Question

Work:

,MAX(CASE WHEN MAX(HIST)
      AND workid IS NOT NULL 
     AND ROLE = 'red' 
       THEN 'ASSIGNED'
         ELSE 'UNASSIGNED'
         END)
            OVER (PARTITION BY id) AS ASSIGNED 

Criteria:

Partition By ID

Look at last entry from each ID, utilizing the PKHistid column

If Role = Red and Workid IS NOT NULL from the last entry for each ID

Then Assigned

Else Unassigned

Table:

PKHistid    ID    Role       Entry_Date    Workid
 1          101    Red       1/1/17         201
 2          101    Yellow    1/2/17         201
 3          102    Yellow    5/1/17        (Null)
 4          102    Red       6/1/17         202
 5          103    Red       7/1/17         202
 6          103    Red       7/5/17         202

Expected Results: (New Column Assigned_Status)

PKHistid    ID    Role       Entry_Date    Workid    *Assigned_Status
 1          101    Red       1/1/17         201         Unassigned
 2          101    Yellow    1/2/17         201         Unassigned
 3          102    Yellow    5/1/17        (Null)       Assigned
 4          102    Red       6/1/17         202         Assigned
 5          103    Red       7/1/17         202         Assigned
 6          103    Red       7/5/17         202         Assigned

Answer 1

Is this "instead of" your earlier question (also posted today), or is it "in addition to" it? If it is "in addition to", note that you can do both things in the same query.

Here you need a case expression to create the additional column. In the case expression, the condition uses an analytic function. I prefer the analytic version of the LAST function (which, unfortunately, many developers don't seem to know and use). Please read the Oracle documentation for it if it is not familiar to you.

Note that analytic functions can't be nested; but there is absolutely no prohibition against using analytic functions in case expressions. I often see solutions where the analytic function is called in a subquery, and then further processing (such as case expressions using the result from the analytic functions) is done in an outer query. Unnecessary layering!

with
  inputs ( pkhistid, id, role, entry_date, workid) as (
    select 1, 101, 'Red'   , to_date('1/1/17', 'mm/dd/rr'), 201  from dual union all
    select 2, 101, 'Yellow', to_date('1/2/17', 'mm/dd/rr'), 201  from dual union all
    select 3, 102, 'Yellow', to_date('5/1/17', 'mm/dd/rr'), null from dual union all
    select 4, 102, 'Red'   , to_date('6/1/17', 'mm/dd/rr'), 202  from dual union all
    select 5, 103, 'Red'   , to_date('7/1/17', 'mm/dd/rr'), 202  from dual union all
    select 6, 103, 'Red'   , to_date('7/5/17', 'mm/dd/rr'), 202  from dual
  )
-- End of simulated inputs (for testing only, not part of the solution).
-- SQL query begins BELOW THIS LINE. Use your actual table and column names.
select   pkhistid, id, role, entry_date, workid,
         case when max(role)   keep (dense_rank last order by pkhistid)
                               over (partition by id) = 'Red'
                   and
                   max(workid) keep (dense_rank last order by pkhistid)
                               over (partition by id) is not null
              then 'Assigned'
              else 'Unassigned' end as assigned_status
from     inputs
order by id, pkhistid -- If needed
;

  PKHISTID         ID ROLE   ENTRY_DATE     WORKID ASSIGNED_STATUS
---------- ---------- ------ ---------- ---------- ---------------
         1        101 Red    01/01/17          201 Unassigned
         2        101 Yellow 01/02/17          201 Unassigned
         3        102 Yellow 05/01/17              Assigned  
         4        102 Red    06/01/17          202 Assigned  
         5        103 Red    07/01/17          202 Assigned  
         6        103 Red    07/05/17          202 Assigned