create new variable from string variable and a time variable (eg. term) in SQL

Question

I am trying to derive a variable based on two other variables from two different tables. I want to know a student's program in their ending cohort term (i.e. the term they left the college, regardless of graduation). I think coalesce may be the wrong function. Here is my code:

select distinct s.colleagueId,st.term, 
coalesce(EndProgram.EndProgram) EndProgram
from tbl_studentTerms st 
inner join dbo.tbl_Terms t
  on st.term = t.term
  inner join
   tbl_students s
   on st.colleagueId = s.colleagueId 

-- Ending cohort program

left join (
select st.activeProgram as EndProgram
from tbl_studentTerms st
inner join tbl_students s
on s.colleagueId=st.colleagueId
where st.term = s.endingCohort) EndProgram
on EndProgram.colleagueId = s.colleagueId

Answer 1

The final line in your End Cohort Program section, you're joining "on EndProgram.colleagueId = s.colleagueId" - however, the select statement that you have labelled EndProgram does not return the ColleagueID column, so you are unable to join on it. If you add it, you may solve that particular problem:

left join (
select st.activeProgram as EndProgram,
s.colleagueId as ColleagueID
from tbl_studentTerms st
inner join tbl_students s
on s.colleagueId=st.colleagueId
where st.term = s.endingCohort) EndProgram
on EndProgram.colleagueId = s.colleagueId