Tags:

APEX Validation: Check overlapping time periods

Von Tobias Arnhold 1.20.2014
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:
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.

Post Tags:

2 Comments " APEX Validation: Check overlapping time periods "

Alex Nuijten 21 January, 2014 10:07

But will it work on a multi-user application? You might create an entry which is being created by someone else at the same time...

Unknown 21 January, 2014 11:14

Hi Tobias,

To check overlapping periods in Oracle Database you can use the function WM_OVERLAPS (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#g1014747)
With this function you get, in my opinion, easy to ready code.


WM_OVERLAPS(
WM_PERIOD(
VALID_FROM,
VALID_UNTIL
),
WM_PERIOD(
TO_DATE(:P6_FROM,'DD.MM.YYYY'),
TO_DATE(:P6_UNTIL,'DD.MM.YYYY')
)
) = 1


Best regards,

Carlos Pereira