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?

• Thom A

  SSC Guru

  Points: 99009

  More actions

  August 14, 2017 at 2:04 am

  #1955109

  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