CODEWITHSUNDEEP
phpmysqlsql
nithinTa
nithinTa

Reputation: 1642

How to check time allotment overlap in table (mysql)?

I've a time allotment table in mysql. Before i push new record into it i need to check whether it overlap an existing date and time period.

Sample data in db are following

Start Date   End Date   | Start Time | End Time
2017-01-01 | 2017-01-01 | 10:00      | 11:00 
2017-01-01 | 2017-01-01 | 08:00      | 09:00
2017-01-01 | 2017-01-02 | 01:00      | 02:00

the last record means both 2017-01-01 and 2017-01-02, hour 01:00 to 02:00 is allotted on both days.

Below are some sample records i will try to push. Expected result is shown as last column. I need to run the query and make sure these records wont overlap in db.

Start Date   End Date   | Start Time  End Time
2017-01-01 | 2017-01-01 | 01:30      | 01:31 | OVERLAP
2017-01-02 | 2017-01-02 | 01:30      | 01:31 | OVERLAP
2017-01-01 | 2017-01-02 | 01:30      | 01:31 | OVERLAP
2017-01-01 | 2017-01-02 | 00:30      | 00:31 | PASS
2017-01-03 | 2017-01-03 | 00:30      | 00:31 | PASS
2016-12-30 | 2017-01-04 | 01:30      | 01:31 | OVERLAP
2016-12-30 | 2016-12-30 | 00:30      | 00:31 | PASS

I was unable to write a query which satisfies all condition. Please help me with the query to check for overlapping records in db, my inputs are $start_date, $end_date, $start_time and $end_time.

Upvotes: 0

Views: 80

Answers (1)

etsa
etsa

Reputation: 5060

I made this query. Pls check it with more values. I did it on MSSQL, but it should be the same for Mysql. Pls contact me for any question. 

 SELECT A.ID, A.START_DATE, A.END_DATE, A.START_TIME, A.END_TIME
, CASE WHEN B.START_DATE IS NULL THEN 'PASS' ELSE 'OVERLAP' END AS CHK
, B.START_DATE,B.END_DATE, B.START_TIME, B.END_TIME
FROM NEW_RECORDS A
LEFT JOIN EXISTING_RECORDS B ON A.START_DATE <=B.END_DATE 
   AND A.END_DATE>=B.START_DATE
   AND A.START_TIME<=B.END_TIME 
   AND A.END_TIME>=B.START_TIME

Output:

+--------+-------------------------+-------------------------+------------------+------------------+---------+
|     ID |       START_DATE        |        END_DATE         |    START_TIME    |     END_TIME     |   CHK   |
+--------+-------------------------+-------------------------+------------------+------------------+---------+
|      1 | 2017-01-01 00:00:00.000 | 2017-01-01 00:00:00.000 | 01:30:00.0000000 | 01:31:00.0000000 | OVERLAP |
|      2 | 2017-01-02 00:00:00.000 | 2017-01-02 00:00:00.000 | 01:30:00.0000000 | 01:31:00.0000000 | OVERLAP |
|      3 | 2017-01-01 00:00:00.000 | 2017-01-02 00:00:00.000 | 01:30:00.0000000 | 01:31:00.0000000 | OVERLAP |
|      4 | 2017-01-01 00:00:00.000 | 2017-01-02 00:00:00.000 | 00:30:00.0000000 | 00:31:00.0000000 | PASS    |
|      5 | 2017-01-03 00:00:00.000 | 2017-01-03 00:00:00.000 | 00:30:00.0000000 | 00:31:00.0000000 | PASS    |
|      6 | 2016-12-30 00:00:00.000 | 2017-01-04 00:00:00.000 | 01:30:00.0000000 | 01:31:00.0000000 | OVERLAP |
|      7 | 2016-12-30 00:00:00.000 | 2016-12-30 00:00:00.000 | 00:30:00.0000000 | 00:31:00.0000000 | PASS    |
+--------+-------------------------+-------------------------+------------------+------------------+---------+

Upvotes: 1

Related Questions