Hi All,
Per the query below, I'd like to ONLY output results from entries from the previous month (in this case it's July) based off SYSDATE.
I suspect I need a "WHERE" statement that only returns the previous month. That said, I'm having a heck of a time to get it to work.
I'm using ORACLE 11g Express and SQL*Plus.
Any help would be greatly appreciated.
QUERY:
SELECT reportnum "REPORT #", datechecked "ISSUED",
faultreport.licenseno "PLATE #", make, model,
vehicle.year, lname || ', ' || fname “ISSUING EMPLOYEE”
FROM faultreport
JOIN employee ON employee.empno = faultreport.empno
JOIN vehicle ON vehicle.licenseno = faultreport.licenseno
ORDER BY reportnum;
CURRENT OUTPUT:
REPORT # ISSUED PLATE # MAKE MODEL YEAR ISSUING EMPLOYEE
---------- --------- ------- ------------ --------------- ---------- ----------------------
1000000001 03-JUL-13 BH05J9U Ford Escape 2013 Smith, Sandy
1000000002 04-JUL-13 GI0UJD7 Ford Fusion 2012 Smith, Sandy
1000000003 05-JUL-13 5HDI9TG Ford Fiesta 2013 Miller, Harry
1000000005 08-JUN-13 A12F8GH Chevrolet Cruze 2013 Weaver, Brendan
DESIRED OUTPUT:
REPORT # ISSUED PLATE # MAKE MODEL YEAR ISSUING EMPLOYEE
---------- --------- ------- ------------ --------------- ---------- ----------------------
1000000001 03-JUL-13 BH05J9U Ford Escape 2013 Smith, Sandy
1000000002 04-JUL-13 GI0UJD7 Ford Fusion 2012 Smith, Sandy
1000000003 05-JUL-13 5HDI9TG Ford Fiesta 2013 Miller, Harry
SAMPLE DATA:
CREATE TABLE EMPLOYEE
(EmpNo NUMBER(6) PRIMARY KEY,
Title VARCHAR2(30),
Fname VARCHAR2(10) NOT NULL,
Lname VARCHAR2(10) NOT NULL,
OutNo NUMBER(4),
SupervisorNo NUMBER(6));
CREATE TABLE VEHICLE
(LicenseNo VARCHAR2(7) PRIMARY KEY,
Make VARCHAR2(12),
Model VARCHAR2(15),
Color VARCHAR2(15),
Year NUMBER(4),
NoDoors NUMBER(2),
Capacity NUMBER(2),
DailyRate NUMBER(4),
outNo NUMBER(4));
CREATE TABLE FAULTREPORT
(ReportNum NUMBER(10) PRIMARY KEY,
DateChecked DATE,
EmpNo NUMBER(6),
LicenseNo VARCHAR2(7),
RentalNo NUMBER(10),
Comments VARCHAR2(200));
INSERT INTO EMPLOYEE VALUES ('100001','Associate','Jenny','Smith','1001','100007');
INSERT INTO EMPLOYEE VALUES ('100002','Associate','Fred','Frapples','1001','100007');
INSERT INTO EMPLOYEE VALUES ('100006','Associate','Bruce','Peer','1002','100009');
INSERT INTO EMPLOYEE VALUES ('100007','Manager','Sandy','Smith','1001','100003');
INSERT INTO EMPLOYEE VALUES ('100008','Associate','Julie','Walker','1003','100010');
INSERT INTO EMPLOYEE VALUES ('100009','Manager','Brendan','Weaver','1002','100003');
INSERT INTO EMPLOYEE VALUES ('100010','Manager','Wendy','Hill','1003','100003');
INSERT INTO EMPLOYEE VALUES ('100011','Associate','Jeorge','Peer','1004','100012');
INSERT INTO EMPLOYEE VALUES ('100012','Manager','Eric','Green','1004','100003');
INSERT INTO FAULTREPORT VALUES ('1000000001','3-Jul-13','100007','BH05J9U','3000000001','');
INSERT INTO FAULTREPORT VALUES ('1000000002','4-Jul-13','100007','GI0UJD7','3000000004','');
INSERT INTO FAULTREPORT VALUES ('1000000003','5-Jul-13','100004','5HDI9TG','3000000003','');
INSERT INTO FAULTREPORT VALUES ('1000000004','7-Jun-13','100003','GI0UJD7','3000000004','');
INSERT INTO FAULTREPORT VALUES ('1000000005','8-Jun-13','100009','A12F8GH','3000000005','');
INSERT INTO FAULTREPORT VALUES ('1000000006','4-May-13','100009','S5BNP2S','3000000006','');
INSERT INTO FAULTREPORT VALUES ('1000000007','5-May-13','100003','XCH4Y23','3000000007','');
INSERT INTO FAULTREPORT VALUES ('1000000008','6-May-13','100010','A12F8GH','3000000005','');
INSERT INTO FAULTREPORT VALUES ('1000000009','2-Apr-13','100003','A12F8GH','3000000005','');
INSERT INTO FAULTREPORT VALUES ('1000000010','3-Apr-13','100003','634HV2E','3000000010','');
INSERT INTO VEHICLE VALUES ('BH05J9U','Ford','Escape','White','2013','4','5','120','1001');
INSERT INTO VEHICLE VALUES ('H4L0DH8','Ford','Edge','Blue','2012','4','5','120','1001');
INSERT INTO VEHICLE VALUES ('5HDI9TG','Ford','Fiesta','Green','2013','4','5','90','1002');
INSERT INTO VEHICLE VALUES ('GI0UJD7','Ford','Fusion','Yellow','2012','4','5','90','1002');
INSERT INTO VEHICLE VALUES ('GJU4Y7D','Nissian','Versa','Teal','2012','4','5','90','1001');
INSERT INTO VEHICLE VALUES ('S5BNP2S','Nissian','Altima','Blue','2013','4','5','120','1003');
INSERT INTO VEHICLE VALUES ('XCH4Y23','Nissian','Maxima','Black','2012','4','5','120','1002');
INSERT INTO VEHICLE VALUES ('A12F8GH','Chevrolet','Cruze','Black','2013','4','5','90','1003');
INSERT INTO VEHICLE VALUES ('C4YUTSA','Chevrolet','Malibu','White','2012','4','5','120','1004');
INSERT INTO VEHICLE VALUES ('634HV2E','Chevrolet','Impala','Orange','2013','4','5','120','1004');