Question on SQL statement

LabKey Support Forum (Inactive)
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