Tags

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


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
logic-for-sum-vi-puzzle-msbiskills

logic-for-sum-vi-puzzle-msbiskills

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.
  • 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 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

Advertisements