Quick suggestion

😎

USE tempdb;

GO

SET NOCOUNT ON;

DECLARE @test-2 TABLE

(

id int,

knt INT,

name VARCHAR(10),

dugu decimal(18,2),

potr decimal(18,2)

)

INSERT INTO @test-2(id,knt,name,dugu,potr)

VALUES

(1,2010001,'xxx',100, 0 ),

(2,2010001,'yyy',70, 0 ),

(3,2010001,'kkk',0, 60 ),

(4,2010001,'aaa',40, 0 ),

(5,2010001,'bbb',0, 70 ),

(6,2010001,'ccc',0, 30 );

;WITH BASE_DATA AS

(

SELECT

T.id

,T.knt

,T.name

,T.dugu

,SUM(T.potr) OVER

(

PARTITION BY (SELECT NULL)

)

-SUM(T.dugu) OVER

(

PARTITION BY (SELECT NULL)

ORDER BY T.id

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS RT_dugu

FROM @test-2 T

)

SELECT

BD.id

,BD.knt

,BD.dugu

,CASE

WHEN BD.RT_dugu > 0 THEN BD.dugu

WHEN BD.dugu > ABS(BD.RT_dugu) THEN (BD.dugu + BD.RT_dugu)

WHEN BD.dugu <= ABS(BD.RT_dugu) THEN 0

END AS covered

,CASE

WHEN BD.RT_dugu >= 0 THEN 0

WHEN BD.dugu > ABS(BD.RT_dugu) THEN ABS(BD.RT_dugu)

WHEN BD.dugu < ABS(BD.RT_dugu) THEN BD.dugu

END AS [not covered]

FROM BASE_DATA BD

WHERE BD.dugu > 0;

Results

id knt dugu covered not covered

--- ----------- ------- --------- ------------

1 2010001 100.00 100.00 0.00

2 2010001 70.00 60.00 10.00

4 2010001 40.00 0.00 40.00