SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Detecting Inter-lapping Date Ranges

    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:
    Code MySQL:
    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
    Code MySQL:
    SELECT
    `CompanyID`,
    COUNT(`CompanyID`) as cnt
    FROM`CompanyTitleHolder`
    GROUP BY `ProspectCompaniesID`
    HAVING cnt > 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.
    Code MySQL:
    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 :P

    P.S. If there is a simplier method, I will take those suggestions too.
    Please...Never describe anything to me using foo and bar.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I understand the logic but how do I compare all the rows to each other.

    Say I had this result

    Code:
    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.
    Please...Never describe anything to me using foo and bar.

  4. #4
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.
    Please...Never describe anything to me using foo and bar.

  5. #5
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Addict
    Join Date
    Nov 2006
    Posts
    206
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Dr John View Post
    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.
    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
    Please...Never describe anything to me using foo and bar.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, thanks dr john for clarifying the count=0 method
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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