SQL Puzzle | The Sum Puzzle – VI

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

Level TyZe Result Results Logic for Results
0 1 X 0
1 5 X 0
2 2 X 0
3 2 Z 10 1+5+2+2
1 8 X 0
2 6 Z 14 8+6
1 20 X 0
2 9 X 0
3 32 X 0
4 91 Z 152 20+9+32+91
2 21 Z 41 21+20
3 30 Z 59 30+20+9

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

Sample Inputs

Level TyZe Result
0 1 X
1 5 X
2 2 X
3 2 Z
1 8 X
2 6 Z
1 20 X
2 9 X
3 32 X
4 91 Z
2 21 Z
3 30 Z

Expected Output

Level TyZe Result Results
0 1 X 0
1 5 X 0
2 2 X 0
3 2 Z 10
1 8 X 0
2 6 Z 14
1 20 X 0
2 9 X 0
3 32 X 0
4 91 Z 152
2 21 Z 41
3 30 Z 59

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 testSuXVI
(
Level TINYINT
,TyZe TINYINT
,Result       CHAR(1)
)
GO

INSERT INTO testSuXVI VALUES
(0,    1      ,'X'),
(1,    5      ,'X'),
(2,    2      ,'X'),
(3,    2      ,'Z'),
(1,    8      ,'X'),
(2,    6      ,'Z'),
(1,    20     ,'X'),
(2,    9      ,'X'),
(3,    32     ,'X'),
(4,    91     ,'Z'),
(2,    21     ,'Z'),
(3,    30     ,'Z')
GO

--

```

SOLUTION – 1

 ``` -- ;WITH CTE AS ( SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM testSuXVI ) ,CTE1 AS ( SELECT e.Level,e.TyZe,e.Result, r.TyZe NewTyZe ,e.rnk FROM CTE e CROSS APPLY ( SELECT SUM(TyZe) TyZe FROM CTE z WHERE z.rnk <= e.rnk AND e.Result = 'Z' )r ) ,CTE2 AS ( SELECT aa.Level,aa.TyZe,aa.Result,aa.rnk, aa.NewTyZe - ISNULL(CASE WHEN aa.NewTyZe IS NULL THEN NULL ELSE er.Re END,0) NewTyZe FROM CTE1 aa OUTER APPLY ( SELECT TOP 1 NewTyZe Re FROM CTE1 bb WHERE bb.rnk 1 ) ,CTE5 AS ( SELECT * FROM CTE3 ct3 OUTER APPLY ( SELECT startrnk , mrnk,newrnk,Result Result1 FROM CTE4 ct4 WHERE ct3.rnk BETWEEN ct4.startrnk AND ct4.mrnk )nm ) SELECT ott.Level , ott.TyZe ,ott.Result , ISNULL( NewTyZe + ISNULL(CASE WHEN rty IS NOT NULL THEN ( SELECT SUM(c5.TyZe) FROM CTE5 c5 WHERE ( c5.rnk BETWEEN ott.newrnk AND ott.startrnk ) AND c5.Level < ott.Level AND c5.Result = 'X' ) ELSE NULL END,0) , '') Results FROM CTE5 ott -- ``` —

SOLUTION – 2

 ``` -- ;With cte1 as( Select *,sum(IsP_Single) OVER (ORDER BY (select ID) ROWS BETWEEN 1 preceding and CURRENT ROW ) NoOFLevelLookBack from ( Select *, iif(count(1) over(partition by PorM)>1,0,1) IsP_Single from ( select *, sum(case when ch='Z' then 1 else 0 end ) OVER (ORDER BY (select ID) ROWS BETWEEN CURRENT ROW and unbounded following) as PorM from ( select row_number() over (order by (select null)) as ID,* from testLogic1 ) t1 )t2 )t3 ) Select z.*,sum(z.Val) over (partition by z.PorM) + t.aaa as sol from cte1 z left join( select max(a.ID) id,Sum( IsNull(b.Val,0))aaa from cte1 a left join cte1 b on (a.PorM+ a.NoOFLevelLookBack)= b.PorM and a.lev>b.lev and a.NoOFLevelLookBack0 group by a.PorM ) t on z.ID=t.id order by z.id -- ``` —

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