Another SQL question - Please assist :)
Thread Starter
Civilian
Joined: Jun 2003
Posts: 8,407
Likes: 1
From: Miami Beach. FL
Senior Member
Joined: Nov 2006
Posts: 165
Likes: 0
From: jax fl
Find all pilots that are either overdue on their flight proficiency check or need to have one within the next three months (a test is required every 6 months). List the pilot’s name, date of last check and the date it should be completed. Use the traditional method to solve the problem.
Thread Starter
Civilian
Joined: Jun 2003
Posts: 8,407
Likes: 1
From: Miami Beach. FL
Finally made some progress 
It will show the pilots dates and thier required dates. I think I might be able to handle the rest. Will respond once I make more progress.
Code:
TTITLE 'RobAir - Pilot Proficiency Test Status' COLUMN pil_pt135_date - HEADING 'Last Proficiency Test' COLUMN req_test_date - HEADING 'Next Proficiency Test' SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date, (ADD_MONTHS(p.pil_pt135_date,6)) req_test_date FROM hartmar.employee e join hartmar.pilot p on e.emp_num = p.emp_num ;
Thread Starter
Civilian
Joined: Jun 2003
Posts: 8,407
Likes: 1
From: Miami Beach. FL
Problem solved:
I figured it out while reading the Oracle 9i: SQL book while I was on the shitter. It's amazing what you can accomplish while you are in there.
Code:
TTITLE 'RobAir - Pilot Proficiency Test Status' COLUMN pil_pt135_date - HEADING 'Last Proficiency Test' COLUMN req_test_date - HEADING 'Next Proficiency Test' SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date, (ADD_MONTHS(p.pil_pt135_date,6)) req_test_date FROM hartmar.employee e join hartmar.pilot p on e.emp_num = p.emp_num WHERE ADD_MONTHS(p.pil_pt135_date,6) < ADD_MONTHS(CURRENT_DATE,3) ;
Senior Member
Joined: Nov 2006
Posts: 165
Likes: 0
From: jax fl
I figured it out while reading the Oracle 9i: SQL book while I was on the shitter. It's amazing what you can accomplish while you are in there.
The ADD_MONTHS is a lot better then just adding the days like I did. I believe I had the same book as you when I took SQL online. For some reason it wound up being the only book I didn't keep from all my programming classes.
h: