There is a easy way to check if a row with overlapping time periods exists inside your table.
We assume that our table is called T_MACHINE with the columns m_id (PK), valid_from and valid_until
All you need to do is to create the following APEX validation:
Validation type: NOT Exist
SQL:
First time I needed this solution it took me half a day for development and testing.
This time same problem but the solution was not available anymore.
What to do? I asked the WWW! Yes, first shot was a goal:
http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap
Solution was ready in less then 30 minutes.
We assume that our table is called T_MACHINE with the columns m_id (PK), valid_from and valid_until
All you need to do is to create the following APEX validation:
Validation type: NOT Exist
SQL:
select 1 from T_MACHINE WHERE (:P6_ID IS NOT NULL AND M_ID != :P6_ID OR :P6_ID IS NULL) AND (VALID_FROM <= TO_DATE(:P6_UNTIL,'DD.MM.YYYY')) and (TO_DATE(:P6_FROM,'DD.MM.YYYY') <= VALID_UNTIL)
First time I needed this solution it took me half a day for development and testing.
This time same problem but the solution was not available anymore.
What to do? I asked the WWW! Yes, first shot was a goal:
http://stackoverflow.com/questions/325933/determine-whether-two-date-ranges-overlap
Solution was ready in less then 30 minutes.
