none
Select minimum date and max date RRS feed

  • Question

  • Create table #Test(Student_Id int, Test_Id int, Taken_Date datetime)

    insert into #Test values(1, 10, '1/1/2019'), (1, 11, '3/1/2019'), (1, 12, '4/10/2019')

                                            ,(2, 20, '6/1/2019'), (2, 21, '7/1/2019')

                                            ,(3, 30, '8/1/2019')

    Create table #Test_Status(Student_Id int, First_Test_Id int, Latest_Test_Id int null, School_Id int)

    Insert into #Test_Status values(1, 10, 12, 100), (1, 12, null, 200)

                                                   ,(2, 20, 21, 100)

                                                      , (3, 30, null, 100)

    Student goes to multiple schools, can take multiple tests. Need to select first test that has earliest date and latest test that is latest in Test_Status table.

    Student             First_Test_Id              Latest_Test_Id

    1                   10                         12

    2                   20                         21

    3                   30                         null




    • Edited by bluepink Thursday, January 23, 2020 2:24 PM
    Thursday, January 23, 2020 2:20 PM

Answers

  • CREATE TABLE #Test
    (
    	Student_Id INT, 
    	Test_Id    INT, 
    	Taken_Date DATETIME
    );
    INSERT INTO #Test
    VALUES
    (1, 10, '1/1/2019'),
    (1, 11, '3/1/2019'),
    (1, 12, '4/10/2019'),
    (2, 20, '6/1/2019'),
    (2, 21, '7/1/2019'),
    (3, 30, '8/1/2019');
    
    WITH First_Last
    AS 
    (
    	SELECT *, 
    		ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY taken_date ASC) AS First_To_Last, 
    		ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY taken_date DESC) AS Last_To_First
    	FROM #Test
    )
         SELECT f.Student_Id, 
                f.Test_Id AS First_Test_Id, 
                NULLIF(l.Test_Id, f.Test_Id) AS Last_Test_Id, 
                f.Taken_Date AS First_Taken_Date, 
                NULLIF(l.Taken_Date, f.Taken_Date) AS Last_Taken_Date
         FROM First_Last f
              INNER JOIN First_Last l ON f.Student_Id = l.Student_Id
                                         AND f.First_To_Last = 1
                                         AND l.Last_To_First = 1;

    Thursday, January 23, 2020 4:49 PM

All replies

  • What have you tried so far?
    Thursday, January 23, 2020 2:22 PM
  • Create table #Test(Student_Id int, Test_Id int, Taken_Date datetime)
    insert into #Test values
    (1, 10, '1/1/2019')
    , (1, 11, '3/1/2019')
    , (1, 12, '4/10/2019')
    ,(2, 20, '6/1/2019'), (2, 21, '7/1/2019')
    ,(3, 30, '8/1/2019')
    
    
    Create table #Test_Status(Student_Id int, First_Test_Id int, Latest_Test_Id int null, School_Id int)
    Insert into #Test_Status values
    (1, 10, 12, 100)
    , (1, 12, null, 200)
     ,(2, 20, 21, 100)
     , (3, 30, null, 100)
     Select t.Student_Id 
     ,min(ts.First_Test_Id) First_Test_Id
     ,max(ts2.Latest_Test_Id) Latest_Test_Id
     from   #Test t
     left join  #Test_Status ts
     on ts.Student_Id=t.Student_Id and t.Test_Id=ts.First_Test_Id
     left join  #Test_Status ts2
     on ts2.Student_Id=t.Student_Id and t.Test_Id=ts2.Latest_Test_Id 
     Group by  t.Student_Id
    
    
    
    drop  table #Test_Status, #Test

    Thursday, January 23, 2020 2:33 PM
    Moderator
  • CREATE TABLE #Test
    (
    	Student_Id INT, 
    	Test_Id    INT, 
    	Taken_Date DATETIME
    );
    INSERT INTO #Test
    VALUES
    (1, 10, '1/1/2019'),
    (1, 11, '3/1/2019'),
    (1, 12, '4/10/2019'),
    (2, 20, '6/1/2019'),
    (2, 21, '7/1/2019'),
    (3, 30, '8/1/2019');
    
    WITH First_Last
    AS 
    (
    	SELECT *, 
    		ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY taken_date ASC) AS First_To_Last, 
    		ROW_NUMBER() OVER(PARTITION BY student_id ORDER BY taken_date DESC) AS Last_To_First
    	FROM #Test
    )
         SELECT f.Student_Id, 
                f.Test_Id AS First_Test_Id, 
                NULLIF(l.Test_Id, f.Test_Id) AS Last_Test_Id, 
                f.Taken_Date AS First_Taken_Date, 
                NULLIF(l.Taken_Date, f.Taken_Date) AS Last_Taken_Date
         FROM First_Last f
              INNER JOIN First_Last l ON f.Student_Id = l.Student_Id
                                         AND f.First_To_Last = 1
                                         AND l.Last_To_First = 1;

    Thursday, January 23, 2020 4:49 PM