SQL Puzzle | The Sum Puzzle – V

You have to write a query that will give us sum of tyse for each S. Detailed logic is given below

logic-for-sum-puzzle-msbiskills

For details please check out the sample input and the expected output below-

Sample Inputs

Expected Output

Rules/Restrictions

• The challenge is to do it with T-SQL statements only.
• 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 testLogicx
(	
	 TySe TINYINT
	,Result	CHAR(1)
)
GO

INSERT INTO testLogicx VALUES
(1	,'R'),	
(5	,'R'),	
(4	,'R'),	
(4	,'S'),	
(8	,'R'),	
(6	,'S'),	
(20	,'R'),	
(9	,'R'),	
(12	,'R'),	
(11	,'S'),	
(11	,'S'),	
(90	,'S')	
GO

--

—

SOLUTION – 1

--


SELECT e.TySe,e.Result, r.type - ISNULL(CASE WHEN r.Type IS NOT NULL THEN LAG(r.Type) OVER (ORDER BY CASE WHEN r.Type IS NULL THEN -1 ELSE r.Type END) END,0) R 
FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
	FROM 
	testLogicx) e
CROSS APPLY
(
	SELECT SUM(TySe) Type FROM (SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk
	FROM 
	testLogicx) z
	WHERE z.rnk <= e.rnk AND e.Result = 'S'	
)r
ORDER BY e.rnk


--

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

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com