Compare Times to avoid interference

m_jundi
m_jundi used Ask the Experts™
on
Hello Experts :
I need an SQL query check some values to avoid duplication or interference for time values, the data saved as number field in oracle DB 8.1 but considered as times, two fields are being used as start time and stop time to register labor , check below example :

- Saved Value :

Start_time        Stop_Time
 13:00                  17:00

- Some Entered Values (using an application) will raise an error because it will be interfered with saved Start & Stop Times :

13:00                   17:00
13:30                   16:00
14:00                   17:00
13:00                   18:00
11:00                   18:00
11:00                   14:00

Thank You
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Sorry but your question doesn't make sense.

Can you explain a little more about the problem to be solved and provide expected results from the data you posted?

Author

Commented:
I have the following Table "A" which has labor transactions and updated through an application, in order to avoid duplication & interference between new & already saved transactions need to query the DB when entering new transactions before update :

Table A :
Empl_No - Number
trans_Date  - Date
Start_time - Number
End_Time - Number

Example :
if a record transaction already saved as follows in table A :10,05/11/2019,10.30,14.00

and new transaction is being entered through the application: 10,05/11/2019,11.00,16.00 , I need to make the application rejects the update because start & end time are within one of the saved period for the same employee

My query for the new transaction before update the database :

SELECT COUNT(*) INTO w_count fromA where
A.trans_date = w_trans_date and
A.empl_no = w_empl_no and
"Need to check the time here"
.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
See if this works for you.  If not, please provide more sample data and expected results.

with mydata as (
	select empl_no,
		to_date(to_char(trans_date,'MM/DD/YYYY') || to_char(start_time,'00.00'),'MM/DD/YYYYHH24.MI') start_date,
		to_date(to_char(trans_date,'MM/DD/YYYY') || to_char(end_time,'00.00'),'MM/DD/YYYYHH24.MI') end_date
	from tablea
)
select count(*)
from mydata
where empl_no=10
and
(to_date('05/11/2019' || to_char(11.00,'00.00'),'MM/DD/YYYYHH24.MI') between start_date and end_date
or to_date('05/11/2019' || to_char(16.00,'00.00'),'MM/DD/YYYYHH24.MI') between start_date and end_date
)
;

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Mark GeerlingsDatabase Administrator

Commented:
This, simpler query should also work:
SELECT COUNT(*) INTO w_count from A where
 A.trans_date = w_trans_date and
 A.empl_no = w_empl_no and
(w_start_time between a.start_time and a.end_time
or w_end_time between a.start_time and a.end_time);

If this query returns 0 , the new values are OK.  If this returns a value larger than 0 (zero), it indicates the new record would be a duplicate or would overlap the times in an existing record.

This query assumes that these four variables:
w_trans_date
w_empl_no
w_start_time
w_end_time
each have the same datatype as the corresponding column in the table that each one is compared to.

Author

Commented:
Mark Geerlings,

If we have the following records :

Saved start & end time : 10.30 - 14.00
If the new record 14.00 - 15.00 , then count will be more than zero and  can't insert ,  correct ?

thanks
ste5anSenior Developer

Commented:
Using between is not really a good idea, because you cannot control the boundaries (inclusive or not) and it ignores the case, that the new interval can entirely cover an existing one.

I prefer

SELECT COUNT(*)
FROM   A
WHERE  A.trans_date = w_trans_date
       AND A.empl_no = w_empl_no
       AND NOT ( w_start_time > A.end_time
               OR w_end_time < A.start_time );

Open in new window

Where the condition in parentheses is the test for no overlap.
Depending on the kind of boundary inclusion, you need additional equals in it.

NOT ( w_start_time > A.end_time  OR w_end_time < A.start_time  ) -- [] closed
NOT ( w_start_time > A.end_time  OR w_end_time <= A.start_time ) -- [[ right open 
NOT ( w_start_time >= A.end_time OR w_end_time < A.start_time  ) -- ]] left open
NOT ( w_start_time >= A.end_time OR w_end_time <= A.start_time ) -- ][ open

Open in new window

Mark GeerlingsDatabase Administrator

Commented:
Yes, you are correct.  This query will return "false positives" if the new start or end times are exactly the same as a previous start or end time:
SELECT COUNT(*) INTO w_count from A where
  A.trans_date = w_trans_date and
  A.empl_no = w_empl_no and
 (w_start_time between a.start_time and a.end_time
 or w_end_time between a.start_time and a.end_time);

So you need to adjust those conditions slightly.  Assuming the start_time and end_time columns are numbers, something this should work:
SELECT COUNT(*) INTO w_count from A where
  A.trans_date = w_trans_date and
  A.empl_no = w_empl_no and
 (w_start_time between a.start_time and a.end_time -1
 or w_end_time between a.start_time -1 and a.end_time);

I disagree with this statement from ste5an: "Using between is not really a good idea, because you cannot control the boundaries (inclusive or not)".  The boundaries are always inclusive with "between" in Oracle, so they are consistent.  It is not that we can't control them, but we do need to remember that.  I forgot to allow for that in my first suggestion.

I'm not sure that you need all four of the comparisons that ste5an suggested, but maybe you do.  I haven't tested all of the possibilities.  I prefer though to express the comparisons positively in queries, if possible.  I find those much easier to understand and test for than the negative (NOT ...) comparisons, especially when both "and" and "or" conditions are included.
ste5anSenior Developer

Commented:
...and it ignores the case, that the new interval can entirely cover an existing one.

Using BETWEEN requires an additional check:

SELECT COUNT(*)
INTO   w_count
FROM   A
WHERE  A.trans_date = w_trans_date
       AND A.empl_no = w_empl_no
       AND (   w_start_time BETWEEN A.start_time AND A.end_time
               OR w_end_time BETWEEN A.start_time AND A.end_time
               OR (   w_start_time < A.start_time
                      AND w_end_time > A.end_time ));

Open in new window

E.g. the existing row [13.00;14.00] and the new interval is [11.00;15.00].

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial