how to get the value when date field is changed?

Get the date when a field was first changed to its current value

• It's a fairly awkward thing to put into words, but I want the row when the item first switched to a tariff/customer. If an item switches customer, then the date of the switch should be returned, regardless of the difference in tariff. If an item switches projects, the date returned must not change, unless the tariff of the new project is not the same as the old one. Not quite sure how I can make it clearer, but I'm open to suggestions. My query looks like this: SET @id = 1; SELECT DISTINCT ip.ItemID, ip.ProjectID, p.TariffID, p.CustomerID, cs.Date FROM item_project ip LEFT JOIN item_project ip1 ON ip.ItemID = ip1.ItemID AND ip.Date < ip1.Date LEFT JOIN project p ON ip.ProjectID = p.ProjectID LEFT JOIN ( SELECT ip.ItemID, ip.Date FROM item_project ip LEFT JOIN item_project ip1 ON ip.ProjectID = ip1.ProjectID AND ip.Date > ip1.Date LEFT JOIN project p ON ip.ProjectID = p.ProjectID WHERE ip.ItemID = @id AND ip1.ItemID IS NULL AND p.CustomerID = ( SELECT p.CustomerID FROM project p LEFT JOIN item_project ip ON p.ProjectID = ip.ProjectID LEFT JOIN item_project ip1 ON ip.ItemID = ip1.ItemID AND ip.Date < ip1.Date WHERE ip.ItemID = @id AND ip1.ItemID IS NULL ) AND p.TariffID = ( SELECT p.TariffID FROM project p LEFT JOIN item_project ip ON p.ProjectID = ip.ProjectID LEFT JOIN item_project ip1 ON ip.ItemID = ip1.ItemID AND ip.Date < ip1.Date WHERE ip.ItemID = @id AND ip1.ItemID IS NULL ) ) AS cs ON ip.ItemID = cs.ItemID WHERE ip.ItemID = @id AND ip1.ItemID IS NULL which gives me "ItemID","ProjectID","TariffID","CustomerID","Date" "1","2","1","1","2010-11-10 00:00:00" which is the wrong date SET @id=2 gives me: "2","2","1","1",NULL Which is correct, apart from the date SET @id=3 gives me: "3","2","1","1",NULL which is also correct, apart from the date. Here's the database CREATE TABLE IF NOT EXISTS `item_project` ( `ID` int(10) unsigned NOT NULL auto_increment, `ItemID` varchar(10) NOT NULL, `ProjectID` int(10) unsigned NOT NULL, `Date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, PRIMARY KEY (`ID`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1; INSERT INTO `item_project` (`ID`, `ItemID`, `ProjectID`, `Date`) VALUES (1, '1', 1, '2010-11-05 00:00:00'), (2, '1', 2, '2010-11-10 00:00:00'), (3, '1', 3, '2010-11-20 00:00:00'), (4, '2', 2, '2010-11-21 00:00:00'), (5, '3', 4, '2010-11-21 00:00:00'), (6, '3', 2, '2010-11-22 00:00:00'), (7, '1', 2, '2010-11-23 00:00:00'), CREATE TABLE IF NOT EXISTS `project` ( `ProjectID` int(10) unsigned NOT NULL auto_increment, `Name` varchar(45) NOT NULL, `TariffID` varchar(45) NOT NULL, `CustomerID` varchar(45) NOT NULL, PRIMARY KEY (`ProjectID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `project` (`ProjectID`, `Name`, `TariffID`, `CustomerID`) VALUES (1, 'Test', '2', '1'), (2, 'Another test', '1', '1'), (3, 'Project1', '1', '1'), (4, 'Main project', '2', '2'); CREATE TABLE IF NOT EXISTS `tariff` ( `TariffID` int(10) unsigned NOT NULL auto_increment, `Tariff` varchar(45) NOT NULL, PRIMARY KEY (`TariffID`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1; INSERT INTO `tariff` (`TariffID`, `Tariff`) VALUES (1, 'Tariff 1'), (2, 'Tariff 2'); Edit: An item can go back to an old tariff or customer. In this case, the date shown should be the date when this happened.

• Answer:

  My previous answer appeared to work with the new dataset too, but I think I understand the problem you're seeing. New proposed solution: SELECT i_general.ItemID, ProjectID, TariffID, CustomerID, the_date FROM ( SELECT ip.ItemID, p.ProjectID, p.TariffID, p.CustomerID FROM item_project ip INNER JOIN project p ON ip.ProjectID = p.ProjectID INNER JOIN tariff t ON p.TariffID = t.TariffID INNER JOIN ( SELECT ip.ItemID, MAX(Date) AS max_date FROM item_project ip GROUP BY ip.ItemID ) ip_max ON ip_max.ItemID=ip.ItemID AND ip_max.max_date=ip.Date ) i_general INNER JOIN ( SELECT ItemID_1 AS ItemID, IF(MAX(Next_Change_Date) IS NULL, MIN(Date_1), MAX(Next_Change_Date)) AS the_date FROM ( SELECT ItemID_1, Date_1, MIN(Date_2) AS Next_Change_Date FROM ( SELECT ip1.ItemID AS ItemID_1, ip1.Date AS Date_1, p1.TariffID AS TariffID_1, p1.CustomerID AS CustomerID_1 FROM item_project ip1 INNER JOIN project p1 ON ip1.ProjectID = p1.ProjectID ) ipp1 LEFT JOIN ( SELECT ip2.ItemID AS ItemID_2, ip2.Date AS Date_2, p2.TariffID AS TariffID_2, p2.CustomerID AS CustomerID_2 FROM item_project ip2 INNER JOIN project p2 ON ip2.ProjectID = p2.ProjectID ) ipp2 ON ItemID_1=ItemID_2 AND Date_1 < Date_2 AND ((TariffID_1!=TariffID_2 OR CustomerID_1!=CustomerID_2) AND Date_2 IS NOT NULL) GROUP BY ItemID_1, Date_1 ) i_date_pair_info GROUP BY ItemID ) i_date_info ON i_date_info.ItemID = i_general.ItemID I think it provides the result you're looking for. It's more robust in that one of the inner subqueries, i_date_pair_info, explicitly pairs each date with all subsequent changes, if there is one. The grouping then eliminates all but the soonest change.