SQL IF condition from other tables

Question

I'm new here in the site and I need a help from you guys. Below is the schema i have which can be run in this site http://sqlfiddle.com/#!3/134c3. The name of my database is vehicle inspections. My question is after this schema.

  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') 

To clarify things. There are two conditions when we issue ticket to the driver.

1. When vehicle is inspected and found defects on any items under Class A or B (tick 'yes'). The ticket status of that is OPEN. On the other hand if all items on Class A and B are tick 'No' it means no defects are found. The ticket Status is CLOSE. Lastly items under Class C or (traffic violations) are tick N/A. Meaning its a mere vehicle inspection

2. Condition No. 2 is where vehicle is stopped because of traffic violation (ex. Over Speeding). Vehicle will NOT be inspected, The distinction of this issued ticket are all items under Class A and B are tick or mark 'N/A' while on Class C is tick either 'yes' or 'no'.

Now I have this SQL code below that can be use in the schema above where it will extract vehicles on its MAX(EventTime) with corresponding Ticket Status.

  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:

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

THIS IS NOT THE CORRECT since on this particular date there were no inspection at all only the driver was caught for OVER SPEEDING (see the schema above) and items under Class A and B are marked N/A.

The correct result should be one step back which is July 1, 2013 and Ticket Status is OPEN since it was a clear inspection. Items under category A and B are inspected and found TIRES are defective and BRAKING SYSTEM has NO defects.

Somehow I was thinking code where if Event.TicketStatus = CLOSE it will examine if it is close because it was inspected or close because its a traffic violation.

Answer 1

Try this.

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

Answer 2

I think you can solve this that way:

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

The subquery is filtering all events in class 1 and 2 (inspection) and where something happened (<> 'N/A'), and it's getting it's maximum date, so it will bring the last occurence of a real inspection of each car. Then, there's the join to bring the state on that date. From what I understood, that's what you want, right?