SQL Query with Case Select

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=@DeezNutz

Student Report

Student Name Result
John 90
Sam 60
Ram 80

Answer

; WITH numbering AS (
   SELECT Name, StudentDOB, ExamAttempt,
                row_number() OVER (PARTITION BY Name ORDER BY ExamAttempt DESC) AS rowno
   FROM  SchoolStudents
)
INSERT StudentReport(StudentName, Result)
   SELECT n.StudentName, n.ExamAttempt
   FROM   numbering n
   JOIN     Students s ON n.Name = s.Name
   WHERE  s.StudentDOB = n.StudentDOB
      AND  n.rowno = 1

Beware that this is an untested solution. Had you provided CREATE TABLE statements for your tables, and your sample data as INSERT statements, I would have been able to test it. Now I wasn't.

Answer

Hi @Mansoor Mohammed ,

Welcome to Microsoft Q&A!

Please refer below two methods and check whether they are working:

--DDL and sample data  
drop table if exists Student,SchoolStudents,StudentReport  
create table Student  
(StudentName varchar(10),  
StudentDOB date,  
Class int)  
  
insert into Student values  
('John', '1/1/2000', 1),  
('Sam', '2/2/2000', 2),  
('Ram' ,'3/3/2000', 3)  
  
create table SchoolStudents  
(StudentName varchar(10),  
StudentDOB date,  
Class int,  
ExamAttempt int,  
Result int)  
  
insert into SchoolStudents values  
('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)  
  
create table StudentReport  
(StudentName varchar(10),  
Result int)  
  
--Method one  
; WITH numbering AS (  
   SELECT StudentName, StudentDOB, ExamAttempt,Result,class,  
                row_number() OVER (PARTITION BY StudentName,StudentDOB,class ORDER BY ExamAttempt DESC) AS rowno  
   FROM  SchoolStudents  
)  
INSERT StudentReport(StudentName, Result)  
   SELECT n.StudentName, n.result  
   FROM   numbering n  
   JOIN     Student s ON n.StudentName = s.StudentName  
   AND  s.StudentDOB = n.StudentDOB AND S.Class=N.Class  
      WHERE  n.rowno = 1  
  
--Method Two  
INSERT StudentReport(StudentName, Result)  
select distinct a.StudentName,a.Result  
 from SchoolStudents a  
inner join Student b on a.Class=b.Class and a.StudentName=b.StudentName and a.StudentDOB=b.StudentDOB  
inner join (select StudentName,max(ExamAttempt) over (PARTITION BY StudentName,StudentDOB,class) max from SchoolStudents ) c   
on a.StudentName=b.StudentName and a.ExamAttempt=c.max  
  
 --Query final result  
 select * from StudentReport

Output:

StudentName	Result  
John	90  
Ram	80  
Sam	60

Best regards
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

SQL Query with Case Select

2 answers