Post reply

t-sql 2012 most current year

  • May 8, 2019 at 4:36 pm

    #3638423

    In the following t-sql 2012, I want to select records from the AMilestone table when

    the schoolyear is the most current year from the ASemester table.

    select *

    from ASemester ASemester

    join AMilestone AMilestone

    on AMilestone.SCHOOLYEAR = ASemester.SCHOOLYEAR

    where ASemester.SCHOOLYEAR = ASemester.max(SCHOOLYEAR)

    The sql listed above does not work. Thus would you show me the t-sql on how to solve my problem?

  • May 8, 2019 at 5:29 pm

    #3638469

    I would just assign it to a variable first.

    DECLARE @year SMALLINT
    SELECT @year = MAX(SCHOOLYEAR)
    FROM ASemester

    SELECT *
    FROM ASemester ASemester
        JOIN AMilestone AMilestone
            ON AMilestone.SCHOOLYEAR = ASemester.SCHOOLYEAR
    WHERE ASemester.SCHOOLYEAR = @year

    I actually came back to post practically the same solution as Johnathan as an alternative if you need to put it in a view.  This includes your join as well.

    SELECT *
    FROM ASemester ASemester
        JOIN AMilestone AMilestone
            ON AMilestone.SCHOOLYEAR = ASemester.SCHOOLYEAR
    WHERE ASemester.SCHOOLYEAR =
    (
        SELECT MAX(SCHOOLYEAR) FROM ASemester
    );

    SELECT quote FROM brain WHERE original = 1
    0 rows returned

  • May 8, 2019 at 5:44 pm

    #3638477 

    SELECT *
      FROM AMilestone
     WHERE AMilestone.SCHOOLYEAR = (SELECT MAX(ASemester.SCHOOLYEAR) FROM ASemester);

  • May 9, 2019 at 8:14 pm

    #3639337

    this solution works

Viewing 4 posts - 1 through 3 (of 3 total)

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