CODEWITHSUNDEEP
sql-servert-sql
Shailendra
Shailendra

Reputation: 1

Find last data (status) with datetime

I am stuck in situation in which I have a data like 

studentId Marks marksgotOn
1         10    2019-12-01
1         10    2019-12-01
2         35    2019-12-02
3         35    2019-12-02
4         90    2019-12-03

I need to find when student got last marksGotOn 

SELECT StudentId, CASE WHEN Marks BETWEEN 0 AND 10 THEN 'Fail'
  WHEN Marks BETWEEN 30 AND 35 THEN 'Pass' 
  WHEN Marks >=90 THEN 'Excellent' ELSE '' END AS ResultStatus
FROM Student

but I need to find when student change their lastResultStatus time.

Upvotes: 0

Views: 45

Answers (2)

Ethan1701
Ethan1701

Reputation: 193

Building on @gotqn's answer, in order to get the latest date on which a student's status changed:

WITH DataSource AS
(
    SELECT StudentId
         ,CASE WHEN Marks BETWEEN 0 AND 10 THEN 'Fail'WHEN Marks BETWEEN 30 AND 35 THEN 'Pass' WHEN Marks >=90 THEN 'Excellent' ELSE '' END AS ResultStatus
         ,marksGotOn 
    FROM Student
)
,
GradeChanges as
(
SELECT *
      ,IIF(LAG(ResultStatus) OVER(PARTITION BY StudentId ORDER BY marksGotOn) <> ResultStatus, 'Changed', 'Not Changed') as GradeChange
FROM DataSource
ORDER BY StudentId
        ,marksGotOn
)

select
    StudentId,
    max(marksgotOn) as LastGradeChange
where
    GradeChange = 'Changed'
group by
    StudentId

Upvotes: 0

gotqn
gotqn

Reputation: 43626

You can use LAG to get the value from the previous row and compare with current one. We are using PARTITION to group the results by student and ordering by marksGotOn.

WITH DataSource AS
(
    SELECT StudentId
         ,CASE WHEN Marks BETWEEN 0 AND 10 THEN 'Fail'WHEN Marks BETWEEN 30 AND 35 THEN 'Pass' WHEN Marks >=90 THEN 'Excellent' ELSE '' END AS ResultStatus
         ,marksGotOn 
    FROM Student
)
SELECT *
      ,IIF(LAG(ResultStatus) OVER(PARTITION BY StudentId ORDER BY marksGotOn) <> ResultStatus, 'Changed', 'Not Changed')
FROM DataSource
ORDER BY StudentId
        ,marksGotOn

Upvotes: 1

Related Questions