| Author |
 Topic  |
|
|
Umar Strong
Starting Member
8 Posts |
 Posted - 07/30/2013 : 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
Sweden
30421 Posts |
Posted - 07/30/2013 : 12:34:22
|
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);
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
Umar Strong
Starting Member
8 Posts |
Posted - 07/30/2013 : 12:41:30
|
The DML worked!
Thanks for the help.
Best Regards! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 07/31/2013 : 02:44:37
|
;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
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
| |
Topic |
|
|
|
SQL - Did my grade improve?