Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL Puzzle | Getting sum of current value, previous value and next value in a single query.

This puzzle is really cool. You have to get sum of current value, previous value and next value in a single query.

Please check out the sample input and the expected output below-

Sample Input

SNo ColA
1 4
2 1
3 3
4 5
5 2
6 6

Expected Output

SNo ColA OP
1 4 5
2 1 8
3 3 9
4 5 10
5 2 13
6 6 8

Rules/Restrictions

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

Script | use below script to create table & insert some sample data


--


Create table SingleQuery (SNo tinyint, ColA tinyint)
GO

--Sample test data
Insert into SingleQuery values (1,4)
Insert into SingleQuery values (2, 1)
Insert into SingleQuery values (3,3)
Insert into SingleQuery values (4,5)
Insert into SingleQuery values (5,2)
Insert into SingleQuery values (6,6)
GO

CREATE CLUSTERED INDEX Ix_SingleQuery ON SingleQuery(SNo)
GO


--

Solution 1


--

SELECT SNo, ColA, ColA + LAG(ColA, 1, 0) OVER (ORDER BY Sno ASC) + Lead(ColA, 1, 0) OVER (ORDER BY Sno ASC) AS OP
FROM SingleQuery A

--

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

Advertisements