I have a table for car services and the service of every car should be after call sold and before Warranty Expired.
Please advise how can I find violated records from following table
CREATE TABLE Tbl_services
(
car_no VARCHAR2(20),
action VARCHAR2(20),
action_date DATE
);
INSERT INTO tbl_services VALUES ('ABC-123','Car Sold',to_date('09-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-1',to_date('10-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-2',to_date('11-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Service-3',to_date('12-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('ABC-123','Warranty Expired',to_date('13-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-0',to_date('06-11-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Car Sold',to_date('01-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-1',to_date('02-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-2',to_date('03-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-3',to_date('04-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Warranty Expired',to_date('05-12-2008','dd-mm-yyyy'));
INSERT INTO tbl_services VALUES ('XYZ-123','Service-4',to_date('06-12-2008','dd-mm-yyyy'));
SELECT * FROM tbl_services;
1 ABC-123 Car Sold 12/9/2008
2 ABC-123 Service-1 12/10/2008
3 ABC-123 Service-2 12/11/2008
4 ABC-123 Service-3 12/12/2008
5 ABC-123 Warranty Expired 12/13/2008
6 XYZ-123 Service-0 11/6/2008
7 XYZ-123 Car Sold 12/1/2008
8 XYZ-123 Service-1 12/2/2008
9 XYZ-123 Service-2 12/3/2008
10 XYZ-123 Service-3 12/4/2008
11 XYZ-123 Warranty Expired 12/5/2008
12 XYZ-123 Service-4 12/6/2008
service 0 & 4 are violated records for car XYZ-123 please advise how to find them in above table?
Wishes
Edited by: weekend79@gmail.com on Nov 23, 2008 11:43 PM
Edited by: weekend79@gmail.com on Nov 23, 2008 11:45 PM