--

;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


--

—

--

;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


--

—