Question on SQL statement | Matthew Bellew | 2010-05-21 11:54 |
Status: Closed | ||
The tricky part is a selecting only the more recent row and handling the case where the animal has always been alone or has has a roommate at some point. * Currently alone, look for the most recent record NumRoommates = 0 AND StartDate = MAX(StartDate) * First record for this animal MIN(StartDate) * Last Roommate MAX(EndDate) ... WHERE NumRoomates <> 0 putting it together, I think something like this (not tested) SELECT RoommateInfo.Id, CASE WHEN LastRoommate IS NULL THEN FirstStart ELSE LastRoommate END AS AloneSince TIMESTAMPDIFF(SQL_TSI_DAY, NOW(), CASE WHEN LastRoommate IS NULL THEN FirstStart ELSE LastRoommate END) AS DaysAlone FROM RoommateInfo INNER JOIN (SELECT Id, MIN(StartDate) AS FirstStart, MAX(StartDate) AS LastStart From RoommateInfo GROUP BY Id) Dates ON RoommateInfo.Id = Dates.Id LEFT OUTER JOIN (SELECT Id, MAX(EndDate) AS LastRoommate FROM RoommateInfo WHERE NumRoommates <> 0 GROUP BY Id) LastR ON RoommateInfo.Id = LastR.Id WHERE RoommateInfo.NumRoommates = 0 AND StartDate = LastStartDate |
||