December 4, 2015 at 12:52 pm
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
December 4, 2015 at 1:56 pm
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
December 4, 2015 at 2:10 pm
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 );
December 7, 2015 at 6:44 am
I'm sorry, I included an aggregation where I shouldn't.
SELECT id
,knt
,dt
,name
,dugu
,potr
,CASE WHEN SUM(potr) OVER() > SUM(dugu) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING)
THEN dugu
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy