oracle - return most recent work permit period

select * from
(select * from your_work_permits_table
order by start_date desc
)
where rownum = 1


if that doesn't work, please provide sample data and expected results

well, without an actual table definition to work with the names are a guess....

select
*
from (
   select *
        , row_number() over(partition by work_order order by end_date DESC NULLS LAST) as rn
   from work_orders
   )
where rn = 1

or, perhaps

select
*
from (
   select *
        , row_number() over(partition by work_order order by end_date DESC) as rn
   from work_orders
   where end_date IS NOT NULL
   )
where rn = 1

I'm assuming the column representing end date is NULL if the work order is incomplete, and that you wouldn't have a NULL start date if there is a non-null end date.

oh, and if you are only after one record in total then my answer isn't relevant

thanks, I like to use START_DATE and END_DATE of a given record.

how does end_date matter given start_date?

IF the question means this:

I want the most recent complete work permit (complete has a non-null end_date) then it is relevant

ORDER BY end_date DESC NULLS LAST, start_date DESC

that is a reasonable answer, but really, the asker should be providing that info, not us guessing at it.

here is the table structure is with actually more dates...

per_visas_permits_f
person_id, work_permit_id, country_code, issue_date, expiry_date, effective_start_date, effective_end_date

1, 111, US,2015-02-01,null, 2015-02-01,4712-12-31, Y

thank you

My best guess is

select *
from (
    select *
    from per_visas_permits_f
    ORDER BY effective_end_date DESC NULLS LAST, effective_start_date DESC
) where rownum = 1

if you only want a total of one row supplied

oh, perhaps not

My best guess #2 is

select *
from (
    select *
    from per_visas_permits_f
    ORDER BY effective_end_date DESC NULLS LAST, effective_start_date DESC
   where effective_end_date < to_date('4712-12-31','YYYY-MM-DD')
) where rownum = 1

not sure why 31st Dec 4712 is being used, perhaps it is a default?

It is Oracle default end date!

Ok, thanks, but it isn't the maximum possible date.

**Oracle 11g R2 Schema Setup**:

    select
      round(date '-4712-1-1','CC') 
     ,round(date '9999-01-01','CC')
    from dual;


    | ROUND(DATE'-4712-1-1','CC') | ROUND(DATE'9999-01-01','CC') |
    |-----------------------------|------------------------------|
    |   January, 01 4700 00:00:00 |   January, 01 10001 00:00:00 |

Select allOpen in new window

see: http://sqlfiddle.com/#!4/9eecb7/5535

shmz -

how does end_date matter given start_date?  portletpaul took a guess at it, but you didn't confirm if his guess was correct or not.

sdstuber,

The database I am working shows default end date as 4712-12-31. I  don't know why, Oracle is new to me. Hopefully Oracle survives by then.
However selecting the last day of year 4712 makes more sense than first day of that year.

Hi Paul,

what I need to do is to make sure I am sending the latest work permit details. that means I send the latest work permit an employee had in the system even if it has expired.

(sdStuber, you are probably right and eff_end_Date may not be relevant)

sample data:

issue_Date | expiry_date |eff_Start_date| eff_end_date
                    |2009-01-01 |2008-01-01 |4712-12-31
                     |2014-03-07 |2010-08-04 |4712-12-31

I need to send the record in bold as it is the latest info we have with regards to this employees work permit.

Thanks

sorry not sure what oracle does with this date (in bold) when the new record is added.

  |2009-01-01 |2008-01-01 |4712-12-31
  |2014-03-07 |2010-08-04 |4712-12-31

I think it will be like this...back to Paul and importance of effective-end_Date :)

 |2009-01-01 |2008-01-01 |2009-01-01
  |2014-03-07 |2010-08-04 |4712-12-31

THANKS