Условие SQL IF из других таблиц

Я новичок здесь, и мне нужна помощь от вас, ребята. Ниже приведена схема, которая может быть запущена на этом сайте http://sqlfiddle.com/#!3/134c3. Имя моей базы данных - это проверки транспортных средств. Мой вопрос после этой схемы.

  CREATE TABLE Car  
  ([CarID] varchar(36), 
   [PlateNo] varchar(6),
   [Package] int);   

  INSERT INTO Car([CarID], [PlateNo], [Package])
  VALUES('A57D4151-BD49-4B44-AF10-000F1C298E05', '8112AG', 4);

  CREATE TABLE Event    
  ([EventID] int, 
   [CarID] varchar(36), 
   [EventTime] smalldatetime, 
   TicketStatus varchar (10)) ;    

 INSERT INTO Event([EventID], [CarID], [EventTime], TicketStatus) 
 VALUES (1, 'A57D4151-BD49-4B44-AF10-000F1C298E05', '20130701', 'Open'),  
        (2, 'A57D4151-BD49-4B44-AF10-000F1C298E05', '20130702', 'Close') ; 

 CREATE TABLE EventDefects
  ([EventDefectsID] int, 
   [EventID] int, 
   [Status] varchar(15), 
   [DefectID] int) ; 

 INSERT INTO EventDefects ([EventDefectsID], [EventID], [Status], [DefectID]) 
 VALUES (1, 1, 'YES', 1),
        (2, 1, 'NO', 2),
        (3, 1, 'N/A', 3),
        (4, 1, 'N/A', 4),
        (5, 2, 'N/A', 1),
        (6, 2, 'N/A', 2),
        (7, 2, 'N/A', 5),
        (8, 2, 'YES', 3),
        (9, 2, 'NO', 4) ;

 CREATE TABLE Defects
   ([DefectID] int, 
    [DefectsName] varchar (36), 
    [DefectClassID] int) ; 

 INSERT INTO Defects ([DefectID], [DefectsName], [DefectClassID]) 
 VALUES (1, 'TYRE', 1),
        (2, 'BRAKING SYSTEM', 1),
        (3, 'OVER SPEEDING', 3),
        (4, 'NOT WEARING SEATBELTS', 3),
        (5, 'MIRRORS AND WINDSCREEN', 2) ;

 CREATE TABLE DefectClass
    ([Description] varchar (15), 
     [DefectClassID] int) ; 

 INSERT INTO DefectClass ([DefectClassID], [Description]) 
 VALUES (1, 'CATEGORY A'),
        (2, 'CATEGORY B'),
        (3, 'CATEGORY C') 

Прояснить ситуацию. Есть два условия, когда мы выдаем билет водителю.

• Когда автомобиль проверяется и обнаруживает дефекты по любым предметам класса А или В (отметьте "да" ). Статус билета ОТКРЫТО. С другой стороны, если все элементы в классах А и В имеют отметку "Нет", это означает, что дефекты не обнаружены. Статус билета CLOSE. Последними элементами класса C или (нарушениями движения) являются галочки N/A. Это означает, что это просто осмотр транспортных средств

• Состояние № 2 - это место, где транспортное средство остановлено из-за нарушения правил дорожного движения (например, превышение скорости). Транспортное средство НЕ будет проверено. В отличии от этого выданного билета все предметы под классами A и B являются отметками или отметками "N/A", в то время как на классе C отмечается "да" или "нет".

Теперь у меня есть этот код SQL ниже, который можно использовать в схеме выше, где он будет извлекать транспортные средства на своем MAX(EventTime) с соответствующим статусом билета.

  Select 
      PlateNo, TicketStatus, [EventTime] 
  FROM 
      (SELECT 
         ROW_NUMBER() OVER (PARTITION BY Event.CarID ORDER BY [EventTime] DESC) AS [index],
         Event.CarID, 
         TicketStatus, 
         [EventTime],
         plateNo
      FROM 
         [Event] 
      Join 
         [Car] ON Event.CarID = Car.CarID) A 
  WHERE [index] = 1

Результат:

RESULT: PlateNo - 8112AG ; EventTime - July 2, 2013; TicketStatus - Close.

ЭТО НЕ ИСПРАВНО, так как в этот конкретный день инспекции вообще не было, только водитель был пойман за СКОРОСТЬ СКОРОСТИ (см. схему выше), а предметы под классами A и B отмечены как N/A.

Правильный результат должен быть на один шаг назад, который составляет 1 июля 2013 года, а статус билета ОТКРЫТО, так как это была четкая проверка. Элементы под категориями A и B проверяются и обнаруживаются, что ШИНЫ неисправны, а система ТОРМОЖЕНИЯ НЕ имеет дефектов.

Как-то я думал код, где, если Event.TicketStatus = CLOSE, он будет проверять, закрыт ли он, потому что он был проверен или закрыт, потому что это нарушение трафика.

Ответы

SELECT
  PlateNo,
  TicketStatus,
  MAX(EventTime)
FROM
  [Event] E
LEFT OUTER JOIN
  [EventDefects] ED ON E.EventID = ED.EventID
LEFT OUTER JOIN
  [Defects] D ON ED.DefectID = D.DefectID
LEFT OUTER JOIN 
  [Car] C ON E.CarID = C.CarID
WHERE ED.Status = 'YES' AND D.DefectClassID <> 3
GROUP BY PlateNo, TicketStatus

SELECT C.PlateNo, E.EventID, E.TicketStatus, E.EventTime 
 FROM Car C
 INNER JOIN Event E ON C.CarID = E.CarID
 INNER  JOIN (
SELECT CarID, MAX(E.EventTime) EventTime FROM Event E
  LEFT JOIN EventDefects ED ON E.EventID = ED.EventID
  LEFT JOIN Defects D ON ED.DefectID = D.DefectID
          WHERE D.DefectClassID IN (1,2) AND ED.Status <> 'N/A'
 GROUP BY CarID
 ) T ON E.CarID = T.CarID AND E.EventTime = T.EventTime