t-sql 2008 get most current record

Question

t-sql 2008 get most current record

dianerstein 8713

SSCrazy

Points: 2197
More actions
August 12, 2017 at 9:30 pm

#339309

I am basically planning to use the following t-sql 2008 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?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99133 More actions · Answer 1

If there is a value for 2018 in the table, what are you expecting to return; the 2018 year? If you are always going to be returning the most recent year, why have your @EndYear variable as a static value, and instead derive it from your data? I would have a punt at this, but I would be guessing; as I can't accessing your data.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk