I have a table data like this:
RowNo Type Formula Amount
------------------------------------
01 0 3000
02 0 2000
03 0 1000
04 1 01+02-03
I'm trying to find a way to update the amount of the last row by formula based on value of previous rows.
The result should be like:
RowNo Type Formula Amount
-----------------------------------
01 0 3000
02 0 2000
03 0 1000
04 1 01+02-03 4000
Could you please suggest some solution? Thanks in advance!
Using Jeff Moden's CSV splitter DelimitedSplit8K
-- Setup table for testing
declare @tbl table
(
RowNo varchar(2),
Type int,
Formula varchar(10),
Amount int
)
-- sample data
insert into @tbl select '01', 0, NULL, 3000
insert into @tbl select '02', 0, NULL, 2000
insert into @tbl select '03', 0, NULL, 1000
insert into @tbl select '04', 1, '01+02-03', NULL
insert into @tbl select '05', 1, '01-02+03', NULL
-- Query
select *
from @tbl t
cross apply
(
select Amt = sum (i.Amount * f.Opr)
from
(
select ItemNumber,
Item = replace(replace(Item, '+', ''), '-', ''),
Opr = case when charindex('-', Item) > 0 then -1 else 1 end
from dbo.DelimitedSplit8K(replace(replace(Formula, '+', ',+'), '-', ',-'), ',')
) f
inner join @tbl i on f.Item = i.RowNo
) a
where t.Type = 1
/* Result :
RowNo Type Formula Amount Amt
----- ----------- ---------- ----------- -----------
04 1 01+02-03 NULL 4000
05 1 01-02+03 NULL 2000
(2 row(s) affected)
*/
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments