T-sql obtain most current record

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?

This might get you some of the way:

declare @endyear smallint=2017;

select *
  from dbo.enroll as e
       inner join dbo.calendar as c
               on c.calendarid=e.calendarid
              and c.startdate<=e.enddate
              and c.enddate>=e.enddate
 where e.endyear=@endyear
   and e.endstatus in (101,105)
   and e.stateexclude=0
   and e.servicetype='P'
   and e.active=1
   and not exists (select 1
                     from dbo.enroll as te
                          inner join dbo.calendar as tc
                                  on tc.calendarid=te.calendarid
                                 and tc.startdate<=te.enddate
                                 and tc.enddate>=te.enddate
                    where te.personid=e.personid
                      and te.endyear>e.endyear
                      and te.endstatus not in (101,105)
                      and te.stateexclude=e.stateexclude
                      and te.servicetype=e.servicetype
                      and te.active=e.active
                  )
;

ps.: consider loosing the nolock if you care about getting correct results.

Thank you for your answer! I will get rid of the no locks.

You mentioned 'This might get you some of the way:'. It sounds like I will not get the entire result with the t-sql you provided. What is missing?

Well you didn't provide appropriate sample data, so I have no way of testing. Nothing is "missing" - it's my best guess at a solution, but you have to test yourself.

1 Like