Tags

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


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

TySe Result R Logic
1 R NULL
5 R NULL
4 R NULL
4 S 14 1+5+4+4
8 R NULL
6 S 14 8+6
20 R NULL
9 R NULL
12 R NULL
11 S 52 20+9+12+11
11 S 11 11
90 S 90 90
logic-for-sum-puzzle-msbiskills

logic-for-sum-puzzle-msbiskills

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

Sample Inputs

TySe Result
1 R
5 R
4 R
4 S
8 R
6 S
20 R
9 R
12 R
11 S
11 S
90 S

Expected Output

TySe Result R
1 R NULL
5 R NULL
4 R NULL
4 S 14
8 R NULL
6 S 14
20 R NULL
9 R NULL
12 R NULL
11 S 52
11 S 11
90 S 90

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

Advertisements