I have the following table that contains can contain 0,1, or many records for each endyear when a query is excuted by personID.
The enrollmentID key is not used by any queries in my company. That column only exists since database tables requie an identity key.
CREATE TABLE [dbo].[Enrollment](
[enrollmentID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[personID] [int] NOT NULL,
[calendarID] [int] NOT NULL,
[grade] varchar NULL,
[active] [bit] NOT NULL,
[startDate] [smalldatetime] NOT NULL,
[startStatus] varchar NULL,
[startComments] varchar NULL,
[endDate] [smalldatetime] NULL,
[endStatus] varchar NULL,
[endYear] [smallint] NOT NULL
What I need to do is to allow is for a parameter called @endYear to be used and matched against the [endYear] column in the Enrollment table. The user will be able to select @endYear = 2018 or @endYear = 2017.
I need to select the most current enrollment record based upon endDate and endStatus = 202 or 205.
The problem is when @endYear=2017. I can not select records by personID if records exist where endyear =2018 and (there is not an endStatus = 202 OR 205).
Thus can you show me the t-sql 2012 on how to accomplish my goal?