I am basically planning to use the following t-sql 2012 for schoolYear for 2017 using parameter called @endYear. I basically want to select schoolYear 2017 records for the most current record that year where endstatus code =101 or 105.
However I do not want to select that school year 2017 record if there is also a record for school year 2018 where the most current record has an endStatus code of anything not equal 101 or 105. In other words I only want to select the most current record for school year 2018 if end the endstatus code =101 or 105.
The following is the sql I have setup so far.
USE TEST
DECLARE @currentYear int = (select endYear from schoolYear where active = 1)
DECLARE @endYear SMALLINT = 2017
SELECT *
FROM (TEST.dbo.Enroll AS Enroll WITH (NOLOCK)
JOIN TEST.dbo.Calendar AS Calendar WITH (NOLOCK)
On Calendar.CalendarID=Enroll.CalendarID
JOIN
(SELECT Enroll.personID,Enroll.endDate, Enroll.endYear, Enrollmen
t.grade,Enroll.endStatus,Enroll.endComments
,row_number()over (partition BY Enroll.personID
ORDER BY (Enroll.endDate)Desc) as RowNum
FROM dbo.Enroll AS Enroll WITH (NOLOCK)
JOIN dbo.Calendar AS CalendarMax WITH (NOLOCK)
ON CalendarMax.calendarID = Enroll.calendarID
WHERE @endYear = @currentYear - 1 and Enroll.endYear=@endYear
Enroll.endStatus IN (101, 105)
AND Enroll.stateExclude = 0
AND Enroll.ServiceType='P'
AND Enroll.active=1
AND Enroll.endDate between CalendarMax.startDate and
CalendarMax.endDate
GROUP BY Enroll.personID,Enroll.endDate,
Enroll.serviceType, Enroll.endYear,Enroll.grade
,Enroll.endStatus)
as MaxEnroll
ON Enroll.personID = MaxEnroll.personID
AND Enroll.endDate = MaxEnroll.endDate
AND Enroll.endYear = MaxEnroll.endYear
AND Enroll.ServiceType= MaxEnroll.ServiceType
AND Enroll.stateExclude= MaxEnroll.stateExclude
AND MaxEnroll.RowNum = 1
)
Thus can you show much to accomplish my goal in t-sql? If not can you show me how to get the most current record for school year 2018 and join it to the sql that I have just listed?