Select Other Data in SELECT CASE

Question

query return StudentId and HaveGift, now i want when (studentId) returned i use another select to find Student info from tbl_Students (StuName,StuLName ,... ). The SQL which return HaveGift No:

CREATE PROCEDURE SelectGiftsTest
    @StudentId              INT,
    @DateMinCur     NVARCHAR(12),
    @DateMaxCur     NVARCHAR(12),
    @DateMinPrev    NVARCHAR(12),
    @DateMaxPrev    NVARCHAR(12)

AS
    WITH Prev AS
(
    SELECT StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint
UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint     WHERE Date>=@DateMinPrev AND Date <= @DateMaxPrev   AND StudentId = @StudentId
      ) as T 
      GROUP BY  StudentId
),
Cur AS 
(
    SELECT StudentId, ISNULL(SUM(Score),0) As HighScoreUser
FROM (SELECT StudentId, Score FROM tbl_ActPoint
UNION ALL
      SELECT StudentId, Score FROM tbl_EvaPoint     WHERE Date>=@DateMinCur AND Date <= @DateMaxCur     AND StudentId = @StudentId
      ) as T 
      GROUP BY  StudentId
) 
SELECT CASE 
        WHEN(Prev.HighScoreUser <= Cur.HighScoreUser)
        THEN 'Yes'
        ELSE 'No'
        END as HaveGift,Prev.StudentId

FROM Prev
INNER JOIN Cur
ON Prev.StudentId = Cur.StudentId
WHERE Prev.StudentId=@StudentId
RETURN 0

i want select student info from tbl_student based on studentId that returned. — , Jun 11 '17 at 06:47

score 0 · Accepted Answer · answered Jun 11 '17 at 06:55

If I understood what you need correctly.

use Join for getting student info from tbl_student based on studentId that returned as next:-

Instead of:-

SELECT CASE 
        WHEN(Prev.HighScoreUser <= Cur.HighScoreUser)
        THEN 'Yes'
        ELSE 'No'
        END as HaveGift,Prev.StudentId

FROM Prev
INNER JOIN Cur
ON Prev.StudentId = Cur.StudentId
WHERE Prev.StudentId=@StudentId

Type:

SELECT CASE 
        WHEN(Prev.HighScoreUser <= Cur.HighScoreUser)
        THEN 'Yes'
        ELSE 'No'
        END as HaveGift,Prev.StudentId
        , std.name, std. .... -- put your columns here that refer to student info 

FROM Prev
INNER JOIN Cur
ON Prev.StudentId = Cur.StudentId
INNER JOIN tbl_student  std
    on std.StudentId = Cur.StudentId
WHERE Prev.StudentId=@StudentId

Esperento57 · Answer 2 · 2017-06-11T07:57:05.367

When a look your code, you sum in prev and in cur the score of tbl_ActPoint table, but your want compare tbl_EvaPoint diff.

I explain me :

you compare A+B (first union) and A+C (second union), you can only compare B and C.

I have modified your query like this :

with Total as (
    SELECT StudentId, 
    sum(case when Date between @DateMinPrev AND @DateMaxPrev then score else end) ScorePrev, 
    sum(case when Date between @DateMinCur AND @DateMaxCur then score else end) ScoreCur, 
    FROM tbl_ActPoint 
    where StudentId = @StudentId and (Date between @DateMinPrev AND @DateMaxPrev or Date between @DateMinCur AND @DateMaxCur)
    group by StudentId
)
select 
CASE WHEN(ScorePrev <= ScoreCur) THEN 'Yes' ELSE 'No' END as HaveGift, tbl_student.* 
from tbl_student std left outer join total on std.StudentId = Total.StudentId

If you want the current score too, you can do it:

with Total as (
    SELECT StudentId, 
    sum(case when Date between @DateMinPrev AND @DateMaxPrev then score else end) ScorePrev, 
    sum(case when Date between @DateMinCur AND @DateMaxCur then score else end) ScoreCur, 
    FROM tbl_ActPoint 
    where StudentId = @StudentId and (Date between @DateMinPrev AND @DateMaxPrev or Date between @DateMinCur AND @DateMaxCur)
    group by StudentId
),
CurrentScoreUser as (
    SELECT StudentId, sum(Score) CurrentScore FROM tbl_ActPoint group by StudentId
)

select 
CASE WHEN(ScorePrev <= ScoreCur) THEN 'Yes' ELSE 'No' END as HaveGift, CurrentScoreUser.CurrentScore, 
tbl_student.* 
from tbl_student std left outer join Total on std.StudentId = Total.StudentId
left outer join CurrentScoreUser on CurrentScoreUser.StudentId=tbl_student.StudentId

Select Other Data in SELECT CASE

2 Answers2