how to add a filter on the partition by

Question

I am using sql oracle.

I have a table with an emplid and an effdt. I want to add a column in my select that gives the current_flag. I thought this query will do:

select emplid,effdt,
    CASE ROW_NUMBER() OVER(PARTITION BY emplid order by effdt desc) 
        WHEN 1 THEN 'Y'
        ELSE 'N' 
    END CURRENT_FLAG
from job_table

The problem is that I want a 'Y' for the highest effdt of an emplid, but the effdt < sysdate.

Example:

emplid  effdt
x   01/01/2014
x   01/01/2015
x   01/01/2016
y   01/01/2014
y   01/01/2015
y   01/01/2016
y   01/01/2020

with my query I have a 'Y'

for the X on 01/01/2014 and
for y on 01/01/2020.

But I want as result:

emplid  effdt   current_flag
x   01/01/2014  N
x   01/01/2015  N
x   01/01/2016  Y
y   01/01/2014  N
y   01/01/2015  N
y   01/01/2016  Y
y   01/01/2020  N

because 01/01/2020 is not current but in the future. or maybe an "F" for the value Y on the date 01/01/2020 to show it is in the Future.

I wanted to put an filter effdt < sysdate on my partition by, but I don't know how.

Answer 1

Try this, please. It must working

If I understand task

   with tbl  as 
    (SELECT 'x' as id, to_date('01/01/2014', 'dd/mm/yyyy') as dt FROM dual
    UNION ALL
    SELECT 'x' as id, to_date('01/01/2015', 'dd/mm/yyyy') as dt FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2015', 'dd/mm/yyyy') FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2016', 'dd/mm/yyyy') FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2020', 'dd/mm/yyyy') FROM dual)
    select id, dt, 
        CASE ROW_NUMBER() OVER(PARTITION BY id order by case when dt > sysdate then to_date('01/01/1970', 'dd/mm/yyyy') else dt end  desc) 
            WHEN 1 THEN 'Y'
            ELSE 'N' 
        END flg
    from tbl

If I understand task, you can 'F' mark date in "case": when effdt < sysdate then 'F'

    with tbl  as 
    (SELECT 'x' as id, to_date('01/01/2014', 'dd/mm/yyyy') as dt FROM dual
    UNION ALL
    SELECT 'x' as id, to_date('01/01/2015', 'dd/mm/yyyy') as dt FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2015', 'dd/mm/yyyy') FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2016', 'dd/mm/yyyy') FROM dual
    UNION ALL
    SELECT 'y' as id, to_date('01/01/2020', 'dd/mm/yyyy') FROM dual)
    select id, dt, 
        CASE WHEN dt > sysdate  THEN 'F'
            WHEN ROW_NUMBER() OVER (PARTITION BY id order by case when dt > sysdate 
                then to_date('01/01/1970', 'dd/mm/yyyy') else dt end  desc) = 1 
            THEN 'Y'
            ELSE 'N' 
        END flg
    from tbl

x   01.01.2015  Y
x   01.01.2014  N
y   01.01.2016  Y
y   01.01.2015  N
y   01.01.2020  F