SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Sub Queries SQL Server Problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fenixmarcus
Starting Member

3 Posts

Posted - 08/24/2013 :  09:38:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/24/2013 :  10:51:54  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

358 Posts

Posted - 08/24/2013 :  12:58:24  Show Profile  Reply with Quote
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
Go to Top of Page

fenixmarcus
Starting Member

3 Posts

Posted - 08/25/2013 :  02:38:14  Show Profile  Reply with Quote
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.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

358 Posts

Posted - 08/25/2013 :  05:04:42  Show Profile  Reply with Quote
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?
Go to Top of Page

fenixmarcus
Starting Member

3 Posts

Posted - 08/25/2013 :  05:40:45  Show Profile  Reply with Quote
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.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

358 Posts

Posted - 08/25/2013 :  06:06:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 08/27/2013 :  01:47:32  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000