INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using the below tables and the logi

Using the below tables and the logi

(OP)
Using the below tables and the logic in the pseudo code, In my SQL query I'm having trouble getting the correct date value when the A.STUDENT is NOT in the LATEST_DATE table. In my query I have the LATEST_DATE table in a left join, and using OR conditions, but it's not correctly getting the correct date when the student does not exist in the LATEST_DATE table.

I have table LATEST_DATE
STU.ID SDATE
1234 03/01/2017
1235 05/01/2015

I also have table ACADEMICS
KEY STUDENT START_DATE TERM
1 1234 01/01/2017 17/SP
2 1234 06/01/2017 17/SU
3 1555 09/01/2016 16/FA
4 1555 01/01/2017 17/SP
...

The logic pseudo code for my query is:

select first A.TERM found when one of the following criteria is TRUE
FROM ACADEMICS A
WHERE if A.STUDENT is in the LATEST_DATE table
and A.START_DATE > LATEST_DATE.SDATE
I want the current A.START_DATE
else if A.STUDENT is NOT in the LATEST_DATE table
I want the current A.START_DATE

The results should be

1234 17/SU
1555 16/FA

RE: Using the below tables and the logi

It would help if you would format your data:

LATEST_DATE table
STUID  SDATE
1234   03/01/2017
1235   05/01/2015

ACADEMICS table
KEY STUDENT START_DATE TERM
 1   1234   01/01/2017 17/SP
 2   1234   06/01/2017 17/SU
 3   1555   09/01/2016 16/FA
 4   1555   01/01/2017 17/SP
 
Also, you said: "I want the current A.START_DATE" and then:

The results should be
1234 17/SU
1555 16/FA

So, should the results include A.START_DATE? Or not?

Are you looking for 2 outcomes? Or one result (probably a UNION query) with both requirements?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Using the below tables and the logi

(OP)
I'm looking for one result per STUDENT from the ACADEMICS table.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close