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

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.

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