Another SQL question - Please assist :)

Reply Subscribe

Thread Tools

Oct 4, 2007 | 08:26 AM

#21

dieneverknowing

Senior Member

Joined: Nov 2006

Posts: 165

Likes: 0

From: jax fl

Ok, there are 6 pilots total, all of which has a pil_pt135_date that is older then the current date.

The dates are:
01-15-07
04-12-07
05-23-07
06-21-07
07-24-07
08-07-07

Those that are older then 6 months from today is the first date I listed (1/15/07). Run this query and you'll get that.

Code:

SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date
FROM hartmar.employee e join hartmar.pilot p on e.emp_num = p.emp_num
WHERE p.pil_pt135_Date < (CURRENT_DATE - 180);

The only downside to this is that it doesn't account for actual months. It's based upon a month = 30 days @ 6 months or 180 days. I honestly don't see your teacher caring that much for an intro to database class. But in the event they care, you'll just need to make the DateADD function work.

Last edited by dieneverknowing; Oct 4, 2007 at 08:31 AM.

Oct 4, 2007 | 09:27 AM

#22

Pete

Thread Starter

Civilian

Joined: Jun 2003

Posts: 8,407

Likes: 1

From: Miami Beach. FL

I'll give it a shot now. Are you working in the online app I provided? Not sure if we can both log in with the same ID at once.

Oct 4, 2007 | 09:49 AM

#23

dieneverknowing

Senior Member

Joined: Nov 2006

Posts: 165

Likes: 0

From: jax fl

Quote:

Originally Posted by Pete

I'll give it a shot now. Are you working in the online app I provided? Not sure if we can both log in with the same ID at once.

I was but the server isn't responding anymore. I can't even load up the log in page

But from what you sent me I think I have it. If you ignore the "tested every six month" you basically need to find the pilots that fit two different ranges.

Range 1: "The Past" <-----------| "Today"

Range 2: "Today" |----------| "3 Months from today"

If you accomplish Range 2, then you accomplish both. If you check for dates that occur before the 3 month point then you are actually checking every date before it including days older then today.

Mega Range: "Past" <----------- "Today" ----------| "3 mos from Today"

So in your 'WHERE' clause check to find dates older then 3 mos from today. Once you get that I'll give you the next part unless you figure that out

Oct 4, 2007 | 12:54 PM

#24

Pete

Thread Starter

Civilian

Joined: Jun 2003

Posts: 8,407

Likes: 1

From: Miami Beach. FL

Quote:

Originally Posted by jclau00

SELECT (E.emp_fname ||' '|| E.emp_lname) Pilot, P.pil_pt135_date, dateadd('mm',6,P.pil_pt135_date)
FROM hartmar.employee E join hartmar.pilot P on E.emp_num=P.emp_num
WHERE dateadd('mm',-3,sysdate) > P.pil_pt135_date

fixed to greater than..too early

Got this error when I tried that:

Quote:

WHERE dateadd('mm',-3,sysdate) > P.pil_pt135_date
*
ERROR at line 3:
ORA-00904: "DATEADD": invalid identifier

Oct 4, 2007 | 01:01 PM

#25

dieneverknowing

Senior Member

Joined: Nov 2006

Posts: 165

Likes: 0

From: jax fl

You don't need the DateAdd to run this query. As well as no extra variables. One mistake coders make is that they quickly will make extra variables then they need. This example shows why you don't need to do that which in the long run will save you storage space and query time.

Try this.

Code:

SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date "Last Proficiency Test", (p.pil_pt135_date + 180) "Next Proficiency Test"
FROM hartmar.employee e join hartmar.pilot p on e.emp_num=P.emp_num
WHERE p.pil_pt135_Date < (CURRENT_DATE + 90);

That should be all you need to find pilots who have an over due date or a date within 3 months. It outputs the Pilot, their last test date and the estimated next test date as per what you sent me.

Oct 4, 2007 | 01:01 PM

#26

M@rshy

٩(̾●̮̮̃̾•̃̾)۶

Joined: Aug 2004

Posts: 21,807

Likes: 0

Quote:

Originally Posted by dieneverknowing

I might have some e-books or reference manuals/quick cards for SQL on my computer at home if either of you are interested.

Sure, that would be cool! Thanks.

I'm learning to add and delete entries in a database now(Via embedded programming) and Visual Studio in my computer lab won't compile anything, I don't think they installed it correctly.

Oct 4, 2007 | 01:04 PM

#27

dieneverknowing

Senior Member

Joined: Nov 2006

Posts: 165

Likes: 0

From: jax fl

Quote:

Originally Posted by MarshyTheKid

No prob. I'll zip it together and host it somewhere either tonight or tomorrow.

What version of VS are you running? And what build errors are you getting?

Oct 4, 2007 | 01:06 PM

#28

M@rshy

٩(̾●̮̮̃̾•̃̾)۶

Joined: Aug 2004

Posts: 21,807

Likes: 0

8.0.5
They aren't build errors, it says "fatal error C1083: Cannot open source file: '.\main.cpp': No such file or directory" Its happened on every computer I've tried it. I'm going to see if my boss will fix it.

Oct 4, 2007 | 01:09 PM

#29

Pete

Thread Starter

Civilian

Joined: Jun 2003

Posts: 8,407

Likes: 1

From: Miami Beach. FL

So I tried to use IF ELSE but I don't think I used it right because I get an error. Not even sure if this is close:

Code:

TTITLE 'RobAir - Pilot Proficiency Test Status'
COLUMN pil_pt135_date -
HEADING 'Last Proficiency Test'
COLUMN req_pt135_date -
HEADING 'Next Proficiency Test'
SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date
FROM hartmar.employee e join hartmar.pilot p on e.emp_num = p.emp_num
WHERE p.pil_pt135_date < (CURRENT_DATE - 180)
IF p.pil_pt135_date < (CURRENT_DATE - 180) 
THEN req_pt135_date = 'OVERDUE'
ELSE req_pt135_date = DATEADD(mm,6,p.pil_pt135_date)
;

Oct 4, 2007 | 01:17 PM

#30

dieneverknowing

Senior Member

Joined: Nov 2006

Posts: 165

Likes: 0

From: jax fl

Quote:

Originally Posted by Pete

So I tried to use IF ELSE but I don't think I used it right because I get an error. Not even sure if this is close:

Code:

TTITLE 'RobAir - Pilot Proficiency Test Status'
COLUMN pil_pt135_date -
HEADING 'Last Proficiency Test'
COLUMN req_pt135_date -
HEADING 'Next Proficiency Test'
SELECT (e.emp_fname ||' '|| e.emp_lname) Pilot, p.pil_pt135_date
FROM hartmar.employee e join hartmar.pilot p on e.emp_num = p.emp_num
WHERE p.pil_pt135_date < (CURRENT_DATE - 180)
IF p.pil_pt135_date < (CURRENT_DATE - 180) 
THEN req_pt135_date = 'OVERDUE'
ELSE req_pt135_date = DATEADD(mm,6,p.pil_pt135_date)
;

Why are you trying to get it to print 'overdue' if that wasn't part of the original task?

Reply Share

First
Prev
3 / 4
Next
Last

Forum Jump