LISTING 2: Query That Returns Repairs and Minimum Registration Dates, Using the COALESCE() Function

SELECT
  repairno,
  COALESCE((SELECT MIN(regdate)
            FROM Repairlog AS R2
            WHERE R2.status = 21 -- Item to workshop
              AND R2.repairno = R1.repairno),
           (SELECT MIN(regdate)
            FROM Repairlog AS R2
            WHERE R2.status = 20 -- Repair in progress
              AND R2.repairno = R1.repairno),
             (SELECT MIN(regdate)
            FROM Repairlog AS R2
            WHERE R2.status = 0  -- Item handed in
              AND R2.repairno = R1.repairno),
             (SELECT MIN(regdate)
            FROM Repairlog AS R2
            WHERE R2.status = 30 -- Item ready
              AND R2.repairno = R1.repairno)) AS min_regdate
FROM Repairlog AS R1
GROUP BY repairno