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