question

MansoorMohammed-9831 avatar image
0 Votes"
MansoorMohammed-9831 asked MelissaMa-msft commented

SQL Query with Case Select

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


sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MansoorMohammed-9831,

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
; 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MansoorMohammed-9831,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.