Using window functions to subtract one column with another

Question

Post reply

Using window functions to subtract one column with another

kdejan87

Right there with Babe

Points: 737
More actions
December 4, 2015 at 12:52 pm

#322875

How can i use window functions to cover one column with another. But total value from one column?
Formula should go like this:
sum(potr) = 160
dugu in 160 covered | [not covered]
-------------------------
=> 100 in 160 = 100 | 0
=> 70 in 60 = 60 | 10
=> 40 in 0 = 0 | 40
---------------------------
I have a table:
DECLARE @TEST TABLE
(
id int,
knt int,
dt date,
name VARCHAR(10),
dugu decimal(18,2),
potr decimal(18,2)
)
INSERT INTO @TEST VALUES
(1,2010001,'20150101','xxx',100, 0),
(2,2010001,'20150201','yyy',70, 0 ),
(3,2010001,'20150301','kkk',0, 60 ),
(4,2010001,'20150401','aaa',40, 0 ),
(5,2010001,'20150501','bbb',0, 70 ),
(6,2010001,'20150601','ccc',0, 30 );
I have made something like this:
with cte
as
(
Select
id,
knt,
dt,
name,
POTR,
DUGU,
case when dugu > 0 then sum(potr) over() else 0 end as sumPotrAll,
CASE
WHEN DUGU = 0 THEN 0
ELSE sum(dugu) over (order by id rows between unbounded preceding and current row ) END as sumDuguByRow
from @TEST
)
select
*,
CASE
WHEN sumPotrAll > sumDuguByRow and dugu <> 0 THEN dugu
WHEN sumDuguByRow > sumPotrAll and dugu > 0 then sumPotrAll
WHEN dugu = 0 and potr > 0 then 0
end as Covered
from cte
I have upload 2 pictures. One is my result and second is what i need to get
Sample at: http://sqlfiddle.com/#!6/c4783/1

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Maybe something like this:

SELECT id

,knt

,dt

,name

,dugu

,potr

,CASE WHEN SUM(potr) OVER() > SUM(dugu) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING)

THEN SUM(dugu) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING)

WHEN SUM(potr) OVER() > SUM(dugu) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

THEN SUM(potr) OVER() - SUM(dugu) OVER(ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)

ELSE 0 END

FROM @TEST

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

kdejan87 Right there with Babe Points: 737 More actions · Answer 2

I've changed data with this it is no good. Logic must be the same for all options

Try your solution with this data

INSERT INTO @TEST VALUES

(1,2010001,'20150101','xxx',0, 30),

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

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

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

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

(6,2010001,'20150601','ccc',100, 0 );

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

I'm sorry, I included an aggregation where I shouldn't.