Loading

SQL Query insert Case

SQL Query insert Case

Mansoor Mohammed

5y1.1kSQL

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Asma Khalid5y ago
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
Kiran Mohanty5y ago
Hi,
Try this.
insert into StudentReport ([Student Name],  [Result])
select distnict StudentName, max(Result)
from SchoolStudents
where StudentName = @Name
group by StudentName
Jignesh Kumar5y ago
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