SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL - Did my grade improve?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Umar Strong
Starting Member

8 Posts

Posted - 07/30/2013 :  12:12:04  Show Profile  Reply with Quote
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
30265 Posts

Posted - 07/30/2013 :  12:34:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

Umar Strong
Starting Member

8 Posts

Posted - 07/30/2013 :  12:41:30  Show Profile  Reply with Quote
The DML worked!

Thanks for the help.

Best Regards!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/31/2013 :  02:44:37  Show Profile  Reply with Quote

;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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000