get first value of a field based on the max of another field while other max fields are also being used

Question

I'm using multiple MAX statements to get the "last activity" for students. Students have an enrollment record for each year they enroll. I get all the correct results with the exception of one field..the school name of the last school the student was enrolled at. The last school can be found by getting the school name from the record that holds the last entry date...but I can't seem to find a solution, or haven't put the code in the correct place..or something. The different ways I've tried are indented comments at the bottom. DECLARE @grade char(2); Set @grade='00'

Select 
SD.[Last_Name],
SD.[First_Name],
SD.[Middle_Name],
SD.[Name_Appendage] as [Name_Suffix],
convert(varchar,cast(SD.[DOB] as date),101) as [Date_Of_Birth],
SD.[Student_Number] as [Student_Number],
Max (SE.[Academic_Year]) as [Academic_Year],
Max (CONVERT(VARCHAR, SE.[Withdrawal_Date], 101)) as 
[Last_Withdrawal_Date],
Max (CONVERT(VARCHAR, SE.[Graduation_Date], 101)) as 
[Graduation_Date],
Max (SD.[DOE_Number]) as [State_Id],
Max (CONVERT(VARCHAR, SE.[Entry_Date], 101)) as [Last_Entry_Date],
Max (SE.[Graduation_Year]) as [Graduation_Year],
Max (CASE when SE.[Grade_Level] > '12' then @grade 
else SE.[Grade_Level]
End) as [Grade_Level]
From
Student_DemographicsCube as SD WITH (NOLOCK),
Student_EnrollmentCube as SE WITH (NOLOCK)
Where
SD.[Student_ID] = SE.[Student_ID] And
SE.[Entry_Date] > cast('12/31/2001' as date) And
SE.[School_Domain] in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,26,27,28,29,30,31)
Group By
SD.[Student_Number],
SD.[First_Name],
SD.[Middle_Name],
SD.[Last_Name],
SD.[Name_Appendage],
SD.[DOB]
Order By
SD.[Student_Number] asc,
Max (SE.[Entry_Date]) asc

Attempted Solutions to last enrolled at school name

/* Select TOP (1) (SE.[School_Name]) from */
/*                Student_EnrollmentCube order by (SE.[Entry_Date) desc ) */
/* (Select SE.[School_Name] from Student_EnrollmentCube as */
/*        ST with (NOLOCK) where @holddate = SE.[Entry_Date]) */
/*SE.[School_Name] as [School_Name] */
/* Max (CASE when Max (SE.[Entry_Date]) = SE.[Entry_Date] */
/*                  then SE.[School_Name] */
/*                else @school */ 
/*            End) as [School_Name] */
/*      Select TOP (1) SE.[School_Name] from */
/*               Student_EnrollmentCube Group by SE.[Student_Number] */
/*                      order by SE.[Entry_Date] desc  */
/*ROW_NUMBER() OVER (PARTITION BY SE. */
/*           [Student_NUmber],SE.[Academic_Year] ORDER BY SD.*/
/*       [Student_Number],SE.[Academic_Year]) as seqnum */
/*               FROM SE.[Academic_Year] t */
/*             ) t */
/*           WHERE seqnum = 1; */ 


/*6/2/17 Code____________________________________________/*
DECLARE @grade char(2);
Set @grade='00'
Select 
SD.[Last_Name],
SD.[First_Name],
SD.[Middle_Name],
SD.[Name_Appendage] as [Name_Suffix],
    convert(varchar,cast(SD.[DOB] as date),101) as [Date_Of_Birth],
SD.[Student_Number] as [Student_Number],
     Max (SE.[Academic_Year]) as [Academic_Year],
convert(varchar,cast(w.[Last_Withdrawal_date] as date),101) as 
     [Latest_Withdrawal_Date] ,
convert(varchar,cast(g.[Last_Graduation_Date] as date),101) as 
     [Latest_Graduation_Date] ,
Max (SD.[DOE_Number]) as [State_Id],
Max (CONVERT(VARCHAR, e.[Entry_Date], 101)) as [Last_Entry_Date],
Max (SE.[Graduation_Year]) as [Graduation_Year],
Max (CASE when SE.[Grade_Level] > '12' then @grade 
     else SE.[Grade_Level]
     End) as [Grade_Level],
e.[School_Name]
From
Student_DemographicsCube SD WITH (NOLOCK),
Student_EnrollmentCube SE WITH (NOLOCK)
cross apply (select top 1 e.[School_Name], e.[Entry_Date] from 
    Student_EnrollmentCube e
    where e.[Student_id] = se.[Student_id] and se.[entry_date] > 
    cast('12/31/2001' as date) order by  e.[Student_ID], e.[Entry_Date] 
    desc) i
cross apply (select top 1 w.[Withdrawal_Date] as [Last_Withdrawal_Date] 
    from Student_EnrollmentCube w
    where w.[Student_id] = se.[Student_id] and se.[entry_date] > 
    cast('12/31/2001' as date) order by w.[Student_id], w.[Withdrawal_Date] 
    desc) w
cross apply (select top 1 g.[Graduation_Date] as [Last_Graduation_Date] 
    from Student_EnrollmentCube g
    where g.[Student_id] = se.[Student_id] and se.[entry_date] > 
    cast('12/31/2001' as date) order by g.[Student_id], g.[Graduation_Date] 
    desc) g

Where
SD.[Student_ID] = SE.[Student_ID] And sd.[student_Number] = 700010068 and
     SE.[Entry_Date] > cast('12/31/2001' as date) And
SE.[School_Domain] in 
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,13,24,25,26,27,28,29,30,31)

Group By
SD.[Student_Number],
SD.[First_Name],
SD.[Middle_Name],
SD.[Last_Name],
SD.[Name_Appendage],
SD.[DOB],
e.[school_name],
w.[Last_Withdrawal_Date],
g.[Last_Graduation_Date]

Order By
SD.[Student_Number] asc,
Max (SE.[Entry_Date]) asc

score 0 · Answer 1 · edited Jul 29 '20 at 18:30

0

Using cross apply() to get the latest row from Student_EnrollmentCube for the latest entry date and school name

declare @grade char(2);
set @grade = '00';

select
    sd.Last_Name
  , sd.First_Name
  , sd.Middle_Name
  , Name_Suffix            = sd.Name_Appendage
  , Date_Of_Birth          = convert(char(10), convert(date,sd.dob), 101)
  , Student_Number         = sd.Student_Number
  , Academic_Year          = max(se.Academic_Year)
  , Latest_Withdrawal_Date = convert(char(10), max(convert(date,se.Withdrawal_date)), 101)
  , Latest_Graduation_Date = convert(char(10), max(convert(date,se.Graduation_Date)), 101)
  , State_Id               = max(sd.doe_Number)
  , x.Last_Entry_Date        
  , x.School_Name            
  , Graduation_Year        = max(se.Graduation_Year)
  , Grade_Level            = case when max(try_convert(int,se.Grade_Level)) > 12 then @grade else se.Grade_Level end)
from Student_DemographicsCube sd
  inner join Student_EnrollmentCube se
    on sd.Student_id = se.Student_id
  cross apply (
     select top 1 
          i.School_Name
        , Last_Entry_Date = i.Entry_Date
     from Student_EnrollmentCube i
     where i.Student_id = si.Student_id
     order by convert(date,i.Entry_Date) desc
    ) x
where sd.student_Number = 700010068
  and se.Entry_Date > convert(date,'20011231')
  and se.School_Domain in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,13,24,25,26,27,28,29,30,31)
group by 
    sd.Student_Number
  , sd.First_Name
  , sd.Middle_Name
  , sd.Last_Name
  , sd.Name_Appendage
  , sd.dob
  , x.Last_Entry_Date
  , x.School_Name
order by 
    sd.Student_Number asc
  , x.Last_Entry_Date asc

Notes / Reference:

Bad habits to kick : using old-style JOINs - Aaron Bertrand
Bad habits to kick : declaring varchar without (length) - Aaron Bertrand
Bad habits : Putting NOLOCK everywhere - Aaron Bertrand
Bad habits to kick : mis-handling date / range queries - Aaron Bertrand
The only truly safe formats for date/time literals in SQL Server, at least for datetime and smalldatetime are: YYYYMMDD and YYYY-MM-DDThh:mm:ss[.nnn]

edited Jul 29 '20 at 18:30

Community

1
1

answered May 30 '17 at 17:38

SqlZim

37,248
6
41
59

I do need max on the fields indicated max...but I only need most recent on school name. Do I take my original code and add the cross apply code to the end of it? – Bill G May 30 '17 at 18:24
I took my original code..added the cross apply after the FROM statement. The code ran, without errors, but I did not get school name to appear in the output. – Bill G May 30 '17 at 20:14
I am not getting school name in the output. If I try to move the cross apply at the end of my original code I get the error incorrect syntax near the word "cross" and incorrect syntax near the word "order", so I assume the cross apply has to be after the FROM statement....it will run if it is there, but no school name is in the output. Any ideas why? – Bill G May 31 '17 at 13:35
@BillG Are you saying `School_Name` is `null`? – SqlZim May 31 '17 at 14:55
No, the column does not appear in the output. – Bill G May 31 '17 at 16:36
@BillG ... You have to add the column to the `select` list... If you look at the query in my answer, you will find `, x.School_Name` in the `select` list. along with `, (convert(varchar(30), x.Entry_Date, 101)) as Last_Entry_Date`. My mistake was `x` was not set as the alias for the `cross apply()` in this version of the query, I have corrected it. – SqlZim May 31 '17 at 16:41
Finally got the correct School name in the output, but now the max entry date is not correct. Why would the max entry date change? – Bill G Jun 01 '17 at 13:05
@BillG Shouldn't be using `max()` on the `Entry_Date`. Just `group by x.Entry_Date` and use `(convert(varchar(30), x.Entry_Date, 101)) as Last_Entry_Date` – SqlZim Jun 01 '17 at 13:09
I don't think that will work because there are many entry dates from the enrollment records...I only want the latest entry date..and the latest withdrawal...and the latest graduation...and the latest school name. I have modified the code and now have "stacked" joins. During testing it was discovered that inconsistent results were being given. Like- The latest withdrawal date is correct for some...not for all..some who should have a withdrawal date showed up with none. Still working on it. – Bill G Jun 02 '17 at 13:48
@BillG The `cross apply()` already returns the latest row for each `student_id`. That is what the `select top 1 ... order by i.Entry_Date desc` is doing. If you post some example data, I can show you a demo. – SqlZim Jun 02 '17 at 15:00
I understand it gets the latest row for each student_id and the corresponding latest entry date...but the latest withdrawal date (and other dates) are in different records. I have tried putting a second cross apply sorting on student and withdrawal date to get the most recent withdrawal date...but it doesn't work for all students. – Bill G Jun 02 '17 at 16:28
I added todays "version" of the code so you could see the multiple cross applys. Are they coded properly? – Bill G Jun 02 '17 at 17:06
@BillG Almost, but the aliases and references are mixed up and the filters are misplaced and extra stuff was added to the order etc. but I updated my answer, that might be what you need. – SqlZim Jun 02 '17 at 17:41

get first value of a field based on the max of another field while other max fields are also being used

1 Answers1