SQL - Did my grade improve? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL - Did my grade improve?

Author

Topic

Umar Strong
Starting Member

8 Posts

Posted - 2013-07-30 : 12:12:04

Hi guys!

I am struggling to set a flag on the data using SQL. In the following dataset, I would like to know if a student's grade improved on a given date - essentially, I am trying to find out the last column, that has a value of 'Y', using SQL.

S_ID TEST_DT SCORE IMPROVE?
---- -------- ---- -------
A-1 1/1/2013 1
A-1 1/2/2013 1
A-1 1/3/2013 1
A-1 1/4/2013 2     Y
A-1 1/5/2013 2
A-1 1/6/2013 2
A-1 1/7/2013 1
A-1 1/8/2013 2     Y
A-1 1/9/2013 2
B-2 1/1/2013 1
B-2 1/2/2013 1
B-2 1/3/2013 1
B-2 1/4/2013 2     Y
B-2 1/5/2013 1
B-2 1/6/2013 1
B-2 1/7/2013 1
B-2 1/8/2013 1
B-2 1/9/2013 2     Y

For your convenience, following are the DDL and DMLs for this data.

CREATE TABLE DBO.STUDENTS_GRADES (S_ID VARCHAR(3), TEST_DT DATETIME, SCORE INTEGER)

INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/1/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/2/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/3/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/4/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/5/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/6/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/7/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/8/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('A-1', '1/9/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/1/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/2/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/3/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/4/2013', 2)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/5/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/6/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/7/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/8/2013', 1)
INSERT INTO DBO.STUDENTS_GRADES VALUES ('B-2', '1/9/2013', 2)

SELECT * FROM DBO.STUDENTS_GRADES

Any help in this regards will be highly appreciated.

Best Regards!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-30 : 12:34:22

[code]DECLARE @Sample TABLE
(
ID VARCHAR(3),
DT DATETIME,
Score INT
);

INSERT @Sample
(
ID,
DT,
SCORE
)
VALUES ('A-1', '1/1/2013', 1),
('A-1', '1/2/2013', 1),
('A-1', '1/3/2013', 1),
('A-1', '1/4/2013', 2),
('A-1', '1/5/2013', 2),
('A-1', '1/6/2013', 2),
('A-1', '1/7/2013', 1),
('A-1', '1/8/2013', 2),
('A-1', '1/9/2013', 2),
('B-2', '1/1/2013', 1),
('B-2', '1/2/2013', 1),
('B-2', '1/3/2013', 1),
('B-2', '1/4/2013', 2),
('B-2', '1/5/2013', 1),
('B-2', '1/6/2013', 1),
('B-2', '1/7/2013', 1),
('B-2', '1/8/2013', 1),
('B-2', '1/9/2013', 2);

-- SwePeso
SELECT s.ID,
s.DT,
s.Score,
CASE
WHEN f.Score < s.Score THEN 'Y'
ELSE ''
END AS [Improve?]
FROM @Sample AS s
OUTER APPLY (
SELECT TOP(1) q.Score
FROM @Sample AS q
WHERE q.ID = s.ID
AND q.DT < s.DT
ORDER BY q.DT DESC
) AS f(Score);[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Umar Strong
Starting Member

8 Posts

Posted - 2013-07-30 : 12:41:30

The DML worked!

Thanks for the help.

Best Regards!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-31 : 02:44:37

[code]
;With CTE
AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY S_ID ORDER BY TEST_DT) AS Seq
FROM DBO.STUDENTS_GRADES
)

SELECT c1.*,
CASE WHEN c1.Score > c2.Score THEN 'Y' ELSE '' END AS [IMPROVE]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.S_ID = c1.S_ID
AND c2.Seq = c1.Seq -1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources