Results 1 to 8 of 8

Thread: Database SQL help needed to check date range clashes!

  1. #1
    Join Date
    Jan 2012
    Posts
    4

    Question Database SQL help needed to check date range clashes!

    Hi everyone, I am working on a company intranet and I'm having some trouble implementing a constraint via an SQL statement. Hope you guys could help? The scenario is written below:

    A person creates a holiday record by entering a date from and date to. For example 5-jan-2012 to 8-jan-2012. This goes through fine but being a bit silly they have also added another record 7-jan-2012 to 15-jan-2012.

    As you can see from the above scenario there is a clash, meaning they would be wasting their own company holidays!

    Is there any chance someone could help edit my SQL statement to stop clashes such as that occurring?

    SQL statement currently used:

    Code:
    $sql = 
    "INSERT into holidays (employee_id, date_from, date_to, total_days, status) 
    VALUES 
    (:employee_id, :date_from, :date_to, :total_days, 0)";
    Any help would be greatly appreciated!

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You have to check before inserting new record if new start or end date is in between existing start and end date.

  3. #3
    Join Date
    Jan 2012
    Posts
    4
    Do you mean with another SQL statement? If so how could I do that?

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    One way to solve this issue is defining check constraints for the column.

  5. #5
    Join Date
    Jan 2012
    Posts
    4
    Hi thanks for your reply, any chance of giving me an example of how to do it?

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    Which rdbms do you use?

  7. #7
    Join Date
    Jan 2012
    Posts
    4
    Well I'm using MAMP which comes with phpmyadmin which involves MYSQL. Thanks by the way!

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    You may find that in MySql documentation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •