Compare period with period and return a result

[Mangaba]

Speak guys, again bringing a doubt!

I have a table that is registered the "removals" of the employee by HR.

I need to know, via Select, if a certain period I type, it will be in the holiday period or other removal that is registered in this table.

For example:
Mangaba needs to travel to work 01/03/2012
When I go to this on the screen, the screen returns a message from style : "In this period the officer X will be on vacation"

I hope I can express myself!

ABS

[Trevisolli]

Brother, beleza?
Send more information for us, type, both information on the same table?
Negative case, you can solve your problem with "exists" on this other table, where you guard the holidays, removal etc.

[DanielNN]

Well ...
if I understood well there is a table of retreat and you want to know if such date corresponds to a period of removal.
From what I understand is simple.

Select all

Select * 
 from afastamentos a 
where param_data BETWEEN a.data_inicial AND a.data_final 

Assuming there is any departure dates or periods of removal in the table.

[Mangaba]

Yes, spacing data is all on a single table (sgrh.afasts).

The table has the input date fields (holiday start), output date (holiday end) and placed a field called active as they can change dates, but due to Chief order, we can not erase the holiday registration history.

Hence the idea was to make it possible when you select a range, this range is sought after in sgrh.afastamento and if you have any "event" in the period he has put, give the message "Impossible ".

[DanielNN]

OPA,

Here is a select that returns the days rated within the reported period (per parameter).
I think it should help:

Select all

 
SELECT DISTINCT x.dia dias_afastados 
  FROM (SELECT data_inicial + LEVEL - 1 dia 
          FROM (SELECT to_date(&p_ini, 'dd/mm/rrrr') data_inicial FROM dual) 
        CONNECT BY LEVEL <= to_date(&p_fim, 'dd/mm/rrrr') - to_date(&p_ini, 'dd/mm/rrrr') + 1) x 
      , afastamento a 
 where x.dia BETWEEN a.data_inicial AND a.data_final 
ORDER BY x.dia; 

[Mangaba]

I'll try Daniel and I'll return the result!