Detecting Inter-lapping Date Ranges

Databases
1

I have a table (shown below) that ideally if I could would make the companyid and the start and end dates unique in such a way that one can input the same companyid so long as the start and end dates do not overlap each other. I haven’t found a way to make this possible so I am attempting to detect any possible user errors just to inform the user of invalid entries.
I’ve established several rules for this table, mainly :

  1. EntryDate < ExpDate
  2. Muiltiple ProspectCompanyID may not have intertwining EntryDate < ExpDate

Right now I’m just trying to see if I can detect these errors. Rule 1 was easy to find, but rule 2 is a tad bit more difficult.
Here is an example of my table:


CREATE TABLE IF NOT EXISTS `CompanyTitleHolder` (
  `CompanyTitleHolderID` int(10) NOT NULL auto_increment,
  `CompanyID` int(10) NOT NULL,
  `MemberID` int(10) NOT NULL,
  `EntryDate` date NOT NULL,
  `ExpDate` date NOT NULL COMMENT 'Expiration Date',
  PRIMARY KEY  (`CompanyTitleHolderID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ownership Table';

Here is my first query which finds all the records where more than one entry is entered per companyID


SELECT
`CompanyID`,
COUNT(`CompanyID`) as cnt
FROM`CompanyTitleHolder`
GROUP BY `ProspectCompaniesID`
HAVING cnt &gt; 1
ORDER BY `ProspectCompaniesID`

My second query needs to iterate through each companyid from the first and manually check that dates are not over-lapping each other.


Pseudocode: iterate through each companyid from first query as $companyid

SELECT
*
FROM`CompanyTitleHolder`
where id = $companyid

pseudocode: look at each record and compare startdate2 is not between startdate1 and enddate2

As you can tell the second part isn’t very well fleshed out at this point. I was wondering if I could combine the two queries as I think* that querying a query is redundant if you do not plan to make many subqueries for a query… (perhaps I am wrong though)

Is there a way to take the start and end date from one row and compare them to other rows without splitting the query. and is it possible to iterate through each owner.

This is rather complex since I’m unable to express the idea very clearly so if you need more specification, don’t hesitate to ask :stuck_out_tongue:

P.S. If there is a simplier method, I will take those suggestions too.

2

overlapping date ranges is a common sql problem that comes up here from time to time

here’s the last thread – http://www.sitepoint.com/forums/showthread.php?t=664040

let me know if that helps or not

3

I understand the logic but how do I compare all the rows to each other.

Say I had this result


ID |   Start |  END
01 |       1 |    100
02 |     100 |    500
03 |       5 |    6
04 |      89 |    100
05 |     488 |    505
06 |     556 |    600
07 |     600 |    700
08 |     800 |    900

Can I compare the first row to the last row so that the results only include those that break the rule (in the case above ID# 01,02,03,04,05)

I’m trying to work with this here: http://www.artfulsoftware.com/infotree/queries.php#798 but I’m not sure that @var checks all the rows, I think it just checks the previous one.

4

maybe I can select all distinct tableid where I can inner join the table to itself thus include all the combination of first and last and do a comparision on that. and the distinct will tell me which ones are conflicting.

5

On your form, ask for the start and end date, and companyid.
do a query to select all where they DO overlap and have that companyid, and count the result ie select count(*)

If count=0, do an insert, otherwise say sorry, can’t use that pair of dates.

6

That is a wonderful user end implementation to prevent errors. I will implement this on the user side as well. On a different note, I was able to create my ‘detector’ so that I can debug should any imported data be erroneous. Thanks :smiley:

7

yes, thanks dr john for clarifying the count=0 method :slight_smile: