SQL Puzzle | Sum of Previous Value to Current Value Puzzle

Puzzle Statement

This puzzle was asked to me via gmail. Here you have to find sum of previous value to current value. Please check sample input and expected output.

Sum of Previous value to current value Puzzle

Sample Input

Expected Output

Script

Use below script to create table and insert sample data into it.

--

CREATE TABLE OneColumn
(
	ID SMALLINT
)

INSERT INTO OneColumn VALUES
(10),(20),(30),(40)
GO


INSERT INTO OneColumn VALUES
(100)
GO

--

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.
Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

SOLUTION 1 | Using Ranking Function

--

;WITH CTE AS
(
	SELECT 
		ID,		
		ROW_NUMBER() OVER (ORDER BY %%Physloc%%) rnk
	FROM
		OneColumn
)
SELECT 
	ID, ID + ISNULL(( SELECT TOP 1 ID FROM CTE c1 WHERE c.rnk - 1 = c1.rnk  ),0) Sumz
	FROM CTE c

--

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal 

Http://MSBISkills.com