Comparing Dates

youngp

I am working on a foster care system and am working on making good data, I need to compare the admitted date and the end date between several records on the same child.

CREATE TABLE IF NOT EXISTS `cx_assignments` (
  `ID` int(12) unsigned NOT NULL auto_increment,
  `Children_ID` int(12) unsigned NOT NULL,
  `StartDate` date NOT NULL,
  `EndDate` date NOT NULL,
  `Notes` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;

--
-- Dumping data for table `cx_assignments`
--

INSERT INTO `cx_assignments` (`ID`, `Children_ID`, `StartDate`, `EndDate`, `Notes`) VALUES
(1, 1, '2010-01-01', '2010-02-05', ''),
(2, 1, '2010-02-06', '2010-05-04', ''),
(3, 1, '2010-05-04', '2010-06-05', ''),
(4, 2, '2010-03-10', '2010-05-04', ''),
(5, 2, '2010-10-24', '2010-12-18', ''),
(6, 3, '2010-01-01', '2010-12-31', ''),
(7, 4, '2010-12-20', '2010-12-21', ''),
(8, 4, '2010-01-24', '2010-02-15', ''),
(9, 4, '2010-03-20', '2010-05-04', ''),
(10, 4, '2010-12-31', '0000-00-00', ''),
(11, 1, '2010-06-06', '0000-00-00', ''),
(12, 2, '2010-12-31', '2010-01-01', '');

That is some example data, "Children_ID" corresponds to a child's record in another table.

I am so far as to know to join the table to itself, but I need some help as to methods of going through the records with only MySQL and returning the records that conflict with each others.

Also the "0000-00-00" are supposed to be infinity.

If anyone has any questions feel free to ask.

bradgrafelman

youngp wrote:
I need to compare the admitted date and the end date between several records on the same child.

Compare them... how? What exactly are you looking for... overlapping records? You never really explained that part in your post.

youngp wrote:
Also the "0000-00-00" are supposed to be infinity.

In other words, you're trying to represent the situation where the record has no ending date? If so, why not just allow NULL values in that column rather than some nonsensical data such as "0000-00-00".

youngp

bradgrafelman;10958700 wrote:
Compare them... how? What exactly are you looking for... overlapping records? You never really explained that part in your post.

I apologize for not being clear enough.

Let's say Child A was admitted to Home 1 on 2010-01-01, and was entered as leaving that home on 2010-02-02, but was admitted into home 2 on 2010-02-01.

That is an overlap and those are the records I am looking for.

In other words, you're trying to represent the situation where the record has no ending date? If so, why not just allow NULL values in that column rather than some nonsensical data such as "0000-00-00".

To fit in to pre-existing coding.

Thanks for your help.

bradgrafelman

I haven't had my morning cup (or three) of coffee yet, but here's a rough guess as to what you want:

SELECT c1.*, c2.*
FROM cx_assignments c1
LEFT JOIN cx_assignments c2
    ON (
      c1.Children_ID = c2.Children_ID
      AND c1.ID != c2.ID
    )
WHERE
  (c1.StartDate > c2.StartDate AND c1.StartDate < c2.EndDate)
  OR (c1.EndDate > c2.StartDate AND c1.EndDate < c2.EndDate)
  OR (c1.StartDate < c2.StartDate AND c1.EndDate > c2.EndDate AND c2.EndDate != '0000-00-00')
  OR (c1.EndDate = '0000-00-00' AND c2.EndDate = '0000-00-00')
GROUP BY c1.ID

Note that depending upon how the overlap occurs, you might get duplicates (e.g. row ID pairs 14 and 1 might show up as well as 1 and 14). You can either tweak the SQL further or take care of that in the PHP side of things. I also can't help but think that the logic would have been simplified a bit if you had used NULL instead of 0000-00-00 for an ending date.

youngp

bradgrafelman;10958759 wrote:
I haven't had my morning cup (or three) of coffee yet, but here's a rough guess as to what you want:
SELECT c1.*, c2.*
FROM cx_assignments c1
LEFT JOIN cx_assignments c2
    ON (
      c1.Children_ID = c2.Children_ID
      AND c1.ID != c2.ID
    )
WHERE
  (c1.StartDate > c2.StartDate AND c1.StartDate < c2.EndDate)
  OR (c1.EndDate > c2.StartDate AND c1.EndDate < c2.EndDate)
  OR (c1.StartDate < c2.StartDate AND c1.EndDate > c2.EndDate AND c2.EndDate != '0000-00-00')
  OR (c1.EndDate = '0000-00-00' AND c2.EndDate = '0000-00-00')
GROUP BY c1.ID
Note that depending upon how the overlap occurs, you might get duplicates (e.g. row ID pairs 14 and 1 might show up as well as 1 and 14). You can either tweak the SQL further or take care of that in the PHP side of things. I also can't help but think that the logic would have been simplified a bit if you had used NULL instead of 0000-00-00 for an ending date.

Thanks for your help, that has given me some ideas! 😃