oracle - return most recent work permit period

Hi,

how to extract the most recent work permit having work permit start date and end date?

thanks
shmzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
0
PortletPaulfreelancerCommented:
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
0
shmzAuthor Commented:
thanks, I like to use START_DATE and END_DATE of a given record.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
how does end_date matter given start_date?
0
PortletPaulfreelancerCommented:
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
0
sdstuberCommented:
that is a reasonable answer, but really, the asker should be providing that info, not us guessing at it.
0
shmzAuthor Commented:
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
0
PortletPaulfreelancerCommented:
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
0
PortletPaulfreelancerCommented:
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?
0
shmzAuthor Commented:
It is Oracle default end date!
0
PortletPaulfreelancerCommented:
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 |

Open in new window

see: http://sqlfiddle.com/#!4/9eecb7/5535
0
sdstuberCommented:
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.
0
shmzAuthor Commented:
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.
0
shmzAuthor Commented:
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
0
shmzAuthor Commented:
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
0
shmzAuthor Commented:
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
0
PortletPaulfreelancerCommented:
You have now specified this as your need:
"what I need to do is to make sure I am sending the latest work permit details"

Which is quite different to your opening question:
"how to extract the most recent work permit having work permit start date and end date?"

and due to this change in requirement, it now appears that the end date isn't relevant. Not sure how sdstuber was able to intuit that so early, but he did.

So, what is "the latest work permit"?
This, I propose, is something you must decide.
I suggest you base it on issue_Date ( then expiry_date & then eff_Start_date as tie breakers)
select *
from (
    select * 
    from per_visas_permits_f
    ORDER BY issue_Date DESC, expiry_date DESC, eff_Start_date DESC
) where rownum = 1

Open in new window

But ultimately it is your decision, you must choose the correct sequence of those dates to order by.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
THANKS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.