December 4, 2015 at 11:56 am
How can i cover col1 with sum(col2)
I have a table:
DECLARE @test-2 TABLE
(
id int,
knt int,
dt date,
name VARCHAR(10),
dugu decimal(18,2),
potr decimal(18,2)
)
INSERT INTO @test-2 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 );
Formula should go like this:
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
---------------------------
I have do 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-2
)
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 6, 2015 at 6:09 pm
Using Recursion
You should be able to do without recursion
;WITH orderedDataCTE AS
(
SELECT t.*
, SerialNumber = ROW_NUMBER() OVER (ORDER BY t.dt, t.Id)
FROM @test-2 t
)
, cumulativeTotalCTE AS
(
-- First Base Record
SELECT t.*
, CumulativeDugu = CAST(ISNULL(t.dugu, 0) AS DECIMAL(18,2))
, CumulativePotr = CAST(ISNULL(t.potr, 0) AS DECIMAL(18,2))
FROM orderedDataCTE t
WHERE t.SerialNumber = 1
UNION ALL
-- Recursive
SELECT data.*
, CumulativeDugu = CAST(ISNULL(data.dugu, 0) + cte.CumulativeDugu AS DECIMAL(18,2))
, CumulativePotr = CAST(ISNULL(data.potr, 0) + cte.CumulativePotr AS DECIMAL(18,2))
FROM cumulativeTotalCTE cte
INNER JOIN orderedDataCTE data
ON data.SerialNumber = cte.SerialNumber + 1
)
SELECT *
, Covered = CASE
WHEN xPotrSum.Value >= cumulativeData.CumulativeDugu
THEN cumulativeData.dugu
WHEN (xPotrSum.Value - (cumulativeData.CumulativeDugu - ISNULL(cumulativeData.dugu, 0))) < 0
THEN 0
ELSE xPotrSum.Value - (cumulativeData.CumulativeDugu - ISNULL(cumulativeData.dugu, 0))
END
FROM cumulativeTotalCTE cumulativeData
CROSS APPLY
(
SELECT SUM(ISNULL(potr, 0))
FROM @test-2
) xPotrSum(Value)
OPTION (MAXRECURSION 1000) ;
Do not understand business rule for Not Covered -- How did u get 110 for Id = 2?
December 6, 2015 at 7:21 pm
This may be better than above -- Post 2012
;WITH dataCTE AS
(
SELECT t.*
, CumulativeDugu = SUM(ISNULL(t.dugu, 0)) OVER (ORDER BY t.dt, t.Id)
, SumPotr = SUM(ISNULL(t.potr, 0)) OVER (ORDER BY t.dt, t.Id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM @test-2 t
)
SELECT data.*
, Covered = CASE
WHEN data.CumulativeDugu <= data.SumPotr
THEN DATA.dugu
WHEN (data.SumPotr - (data.CumulativeDugu - ISNULL(data.dugu, 0))) < 0
THEN 0
ELSE data.SumPotr - (data.CumulativeDugu - ISNULL(data.dugu, 0))
END
FROM dataCTE data ;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply