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

Kiran Mohanty · Answer

Hi, Try this. insert into StudentReport ([Student Name], [Result]) select distnict StudentName, max(Result) from SchoolStudents where StudentName = @Name group by StudentName

Asma Khalid · Answer

Use below query i.e. INSERT INTO StudentReport ([Student Name ], [Result]) SELECT Student.StudentName, SchoolStudents.Result FROM Student INNER JOIN SchoolStudents ON Student.StudentsDOB = SchoolStudents.SchoolStudentsDOB AND Student.StudentsClass = SchoolStudents.SchoolStudentsClass WHERE Student.StudentName = @ Name AND SchoolStudents.[Exam Attempt] = ( SELECT MAX (tbl1.[Exam Attempt]) AS ExamAttempt FROM SchoolStudents AS tbl1 WHERE tbl1.StudentName = Student.StudentName GROUP BY tbl1.StudentName) Mark answer as accepted if helpful Thanks & Regards, Asma Khalid For More Visit: www.asmak9.com or www.bytezaar.com

Jignesh Kumar · Answer

Hi, Please use this query which will resolve your issue, insert into StudentReport select top 1 StudentName, max (Result) from SchoolStudents ss inner join student s on ss.StudentName = s. Name where StudentName=@ Name group by StudentName

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