0

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
marmode
  • 1
  • What are you expecting to see, and what are you currently seeing? Please provide sample outputs, rather than just a sentence describing it. – Xedni Sep 12 '17 at 17:11
  • i get errors. it won't run. in the student terms table each student has one row per term. i want a derived variable (added as a column) that displays their program in their ending cohort term (which is only available in the students table, which has one row per student). I am hoping to add a column with the program they were enrolled in during their ending cohort. i also tried and failed doing a case statement. – marmode Sep 12 '17 at 17:19
  • As I said, please provide data and the errors you're getting. – Xedni Sep 12 '17 at 17:23
  • I'm getting this error, which is based on the last line of code above. Msg 207, Level 16, State 1, Line 284 Invalid column name 'colleagueId'. What data would be helpful? – marmode Sep 12 '17 at 17:29
  • That error indicates that you're referencing a column that doesn't exist. You'll need to look back at your tables and make sure you have column names spelled correctly and that the columns you expect are there, actually are. As far as data goes, if that doesn't solve your problem, it would be useful to see a mock table containing sample data (i.e. what the data you're querying looks like) and what you expect the output to look like. Data, not sentences describing it. – Xedni Sep 12 '17 at 17:32
  • provide table structure, data sample, and expected results. – Horaciux Sep 12 '17 at 17:37

1 Answers1

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
Tsim
  • 11
  • 1