Sub Queries SQL Server Problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sub Queries SQL Server Problem

Author

Topic

fenixmarcus
Starting Member

4 Posts

Posted - 2013-08-24 : 09:38:21

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/28d38. 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.

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

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-24 : 10:51:54

Is this what you're looking at?


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] e
Join [Car] c
ON e.CarID = c.CarID
JOIN EventDefects ed
ON ed.EventID = e.EventID
AND ed.Status <> 'N/A'
JOIN Defects d
ON d.DefectID = ed.DefectID
JOIN DefectClass dc
ON dc.DefectClassID = d.DefectClassID
AND dc.Description IN ('CATEGORY A','CATEGORY B')) A 
WHERE [index] = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-24 : 12:58:24

Alternative:


select plateno
      ,ticketstatus
      ,eventtime
  from (select c.plateno
              ,e.ticketstatus
              ,e.eventtime
              ,row_number() over (partition by e.carid order by eventtime desc) as row
          from (select e.eventid
                  from event as e
                       inner join eventdefects as ed
                               on ed.eventid=e.eventid
                       inner join defects as d
                               on d.defectid=ed.defectid
                       inner join defectclass as dc
                               on dc.defectclassid=d.defectclassid
                 group by e.eventid
                 having sum(case
                               when e.ticketstatus='Open'
                                and ed.status='YES'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )>0
                     or avg(case
                               when ed.status='N/A'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )=1
               ) as t
               inner join event as e
                       on e.eventid=t.eventid
               inner join car as c
                       on c.carid=e.carid
       ) as t
 where row=1

fenixmarcus
Starting Member

4 Posts

Posted - 2013-08-25 : 02:38:14

Very impressive guys,i love this forum. One more thing what if i want to extract data for the SECOND, THIRD, FOURTH, so on and so forth INSPECTIONS with the same system that traffic violations are not included.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-25 : 05:04:42

quote:
Originally posted by fenixmarcus

Very impressive guys,i love this forum. One more thing what if i want to extract data for the SECOND, THIRD, FOURTH, so on and so forth INSPECTIONS with the same system that traffic violations are not included.

I'm not sure I understand exactly what it is you want to do.
Do you want to delete records from existing system, or select "old" data to new table?
Also, should the data be filtered like you described earlier (with the difference, that it should catch "old" data) or should the filter be set to catch "over speeding" as well?

fenixmarcus
Starting Member

4 Posts

Posted - 2013-08-25 : 05:40:45

I want to extract inspections EXCEPT the MIN(EventTime) of each PlateNo. with corresponding TicketStatus and EventTime. In my example at the top, July 1 is the first inspection, i want to exclude this date of inspection, i want the succeeding inspections. The tricky part is to exclude as well the traffic violations since they are not inspected totally. You can find the code how they remove the violations on those two answers at the top. To give you better view. Check this link http://sqlfiddle.com/#!3/9f9a0..... It will redirect you to the schema i made and some additional inspections.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-25 : 06:06:42

quote:
Originally posted by fenixmarcus

I want to extract inspections EXCEPT the MIN(EventTime) of each PlateNo. with corresponding TicketStatus and EventTime. In my example at the top, July 1 is the first inspection, i want to exclude this date of inspection, i want the succeeding inspections. The tricky part is to exclude as well the traffic violations since they are not inspected totally. You can find the code how they remove the violations on those two answers at the top. To give you better view. Check this link http://sqlfiddle.com/#!3/9f9a0..... It will redirect you to the schema i made and some additional inspections.

Well that didn't get me closer to understanding, what it is you want to accomplish.

This sql will select the inspections previous to the latest (max eventtime) violation for each vehicle:


select plateno
      ,ticketstatus
      ,eventtime
  from (select c.plateno
              ,e.ticketstatus
              ,e.eventtime
              ,row_number() over (partition by e.carid order by eventtime desc) as row
          from (select e.eventid
                  from event as e
                       inner join eventdefects as ed
                               on ed.eventid=e.eventid
                       inner join defects as d
                               on d.defectid=ed.defectid
                       inner join defectclass as dc
                               on dc.defectclassid=d.defectclassid
                 group by e.eventid
                 having sum(case
                               when e.ticketstatus='Open'
                                and ed.status='YES'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )>0
                     or avg(case
                               when ed.status='N/A'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )=1
               ) as t
               inner join event as e
                       on e.eventid=t.eventid
               inner join car as c
                       on c.carid=e.carid
       ) as t
 where row>1

This sql will delete (so be carefull) those inspections from the tables event and eventdefects:


declare @deleteevent table (eventid int);
insert
  into @deleteevent
select e.eventid
  from (select e.eventid
              ,row_number() over (partition by e.carid order by eventtime desc) as row
          from (select e.eventid
                  from event as e
                       inner join eventdefects as ed
                               on ed.eventid=e.eventid
                       inner join defects as d
                               on d.defectid=ed.defectid
                       inner join defectclass as dc
                               on dc.defectclassid=d.defectclassid
                 group by e.eventid
                 having sum(case
                               when e.ticketstatus='Open'
                                and ed.status='YES'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )>0
                     or avg(case
                               when ed.status='N/A'
                                and dc.description in ('CATEGORY A','Category B')
                               then 1
                               else 0
                            end
                           )=1
               ) as t
               inner join event as e
                       on e.eventid=t.eventid
               inner join car as c
                       on c.carid=e.carid
       ) as t
       inner join event as e
               on e.eventid=t.eventid
 where t.row>1
;
delete ed
  from @deleteevent as de
       inner join eventdefects as ed
               on ed.eventid=de.eventid
;
delete e
  from @deleteevent as de
       inner join event as e
               on e.eventid=de.eventid
;
delete
  from @deleteevent
;

Hope this was usefull.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-27 : 01:47:32

quote:
Originally posted by fenixmarcus

I want to extract inspections EXCEPT the MIN(EventTime) of each PlateNo. with corresponding TicketStatus and EventTime. In my example at the top, July 1 is the first inspection, i want to exclude this date of inspection, i want the succeeding inspections. The tricky part is to exclude as well the traffic violations since they are not inspected totally. You can find the code how they remove the violations on those two answers at the top. To give you better view. Check this link http://sqlfiddle.com/#!3/9f9a0..... It will redirect you to the schema i made and some additional inspections.

this?


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] e
Join [Car] c
ON e.CarID = c.CarID
JOIN EventDefects ed
ON ed.EventID = e.EventID
AND ed.Status <> 'N/A'
JOIN Defects d
ON d.DefectID = ed.DefectID
JOIN DefectClass dc
ON dc.DefectClassID = d.DefectClassID
AND dc.Description IN ('CATEGORY A','CATEGORY B')) A 
WHERE [index] > 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources