yogi123 (4/11/2013)
Lynn Pettis (4/11/2013)
autoexcrement (4/11/2013)
Sorry, I would like to help, but there are just too many things about all of this that don't make sense to me.For example, why are you (seemingly) storing students more than once in the STUDENT table?
If you alter your design so it is properly normalized, you will eliminate redundant and incoherent data, and it will vastly simplify your queries.
Happens to be a normal occurance in a SIS apparently. It was when I worked at a school district. The student table had one (or more) records for each student enrolled for that school year.
My question is what is the value of S.EndDate when this query was run.
basically we capture changes during a year so Student table has multiple records.
Start and EndDate is for that purpose of track those changes
Quite familiar with this having worked at a k-12 public school district for 5 years. Unfortunately it is difficult to tell you why you got the results you did instead of the expected results since we can't see the data used.
You need to provide us with a reasonable facsimile of your problem (tables, sample data in a readily consummable format -- i.e. cut/paste/run in SSMS) for us to really help.