/**************************************************************/
/*** To Find the Next and Previous Row Value in SQL Server  ***/
/*** Works On: SQL Server 2012, 2014, 2016 ********************/
/**************************************************************/

-- Create a Dummy Table and Insert data
CREATE TABLE ScoreCard(
		ID		INT IDENTITY, 
		Name		VARCHAR(50) NOT NULL,
		Score		INT NOT NULL,
		ScoredOn	SMALLDATETIME NOT NULL);	 
GO
INSERT INTO ScoreCard (Name,Score,ScoredOn)
VALUES 
	('A',80,'2017-01-20'), 
	('A',50,'2017-01-22'),
	('A',65,'2017-01-23'),
	('A',92,'2017-01-25'),
	('A',100,'2017-01-30');

-- Report Requirement 
-- Below is the Expected output
-- Name, ScoredOn, PreviousScore, CurrentScore, NextScore

/*************************/
/*** 2012, 2014, 2016 ****/
/*************************/

SELECT	Name,
	CONVERT(VARCHAR(10),ScoredOn,103) 	AS 'ScoredOn', 
	LAG(s.Score) OVER (ORDER BY s.ID) 	AS 'PreviousScore',
	Score 					AS 'CurrentScore',
	LEAD(s.Score) OVER (ORDER BY s.ID)	AS 'NextScore'
FROM	dbo.ScoreCard s


-- When LEAD and LAG is not available i.e Before 2012
/*************************/
/*** Before 2012 *********/
/*************************/

;WITH SCCTE AS (
	SELECT	s.Name,
		CONVERT(VARCHAR(10),s.ScoredOn,103) AS 'ScoredOn',
		s.Score,
		ROW_NUMBER() OVER (ORDER BY s.ID) AS rownum
	FROM	dbo.ScoreCard s
)
SELECT	SCCTE.Name,
	SCCTE.ScoredOn,
	p.Score		AS 'PreviousScore',
	SCCTE.Score AS 'CurrentScore',
	n.Score		AS 'NextScore'
FROM	SCCTE
LEFT JOIN SCCTE p ON p.rownum = SCCTE.rownum - 1 -- To get Previous row info
LEFT JOIN SCCTE n ON n.rownum = SCCTE.rownum + 1 -- To Get next row info
GO

-- Drop the dummy table
DROP TABLE ScoreCard;