Using the below tables and the logi

[ghbeers]

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

 

[Andrzejek]

It would help if you would format your data:

[pre]
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
[/pre]
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.

 

[ghbeers]

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