SQL Query insert Case

Question

I have two tables

Students and SchoolStudents

 

Student
StudentName	StudentDOB	Class
John	1/1/2000	1
Sam	2/2/2000	2
Ram	3/3/2000	3

 

 

School Students
StudentName	StudentDOB	Class	Exam Attempt		Result
John	1/1/2000	1	1		50
Sam	2/2/2000	2	3		60
John	1/1/2000	1	2		70
Ram	3/3/2000	3	4		80
John	1/1/2000	1	5		90

 

I want to to insert values in StudentReport table

I want a Query passing student name as parameter

insert into StudentReport

Name

Case When

StudentsDOB = SchoolStudentsDOB

AND

StudentsClass and SchoolStudentsClass

Then Result = Max(Exam Attempt)

Where Name=@Name

 

Student Report
Student Name	Result
John	90
Sam	60
Ram	80