November 27, 2015 at 4:01 pm
I want to display the subtraction of two columns. From the first column I need to get sum all value and substract with each value from the second column.
This is the table structure:
id | knt name | dugu | potr |
------------------------------------
1 2010001 | xxx | 100 | 0 |
2 2010001 | yyy | 70 | 0 |
3 2010001 | zzz | 0 | 60 |
4 2010001 | aaa | 40 | 0 |
5 2010001 | bbb | 0 | 70 |
6 2010001 | ccc | 0 | 30 |
--script
DECLARE @test-2 TABLE
(
id int,
knt INT,
name VARCHAR(10),
dugu decimal(18,2),
potr decimal(18,2)
)
INSERT INTO @test-2 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 )
Formula should go:
sum(POTR) = 160
dugu in 160 is covered
-------------------------
=> 100 in 160 = 100 -- sum(dugu) now left 60
=> 70 in 60 = 60 -- sum(dugu) now is 0
=> 40 in 0 = 0
---------------------------
result is covered and not coverd column and Output should be like as:
id | knt | name | dugu | covered| not covered
---------------------------------------------------
1 2010001| xxx | 100 | 100 | 0 |
2 2010001| yyy | 70 | 60 | 10 |
3 2010001| zzz | 40 | 0 | 40 |
Can it be done with window function and how?
November 27, 2015 at 11:00 pm
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply