T-SQL Update field from previous updated

Question

I need to fill field from sum of previous value. For example:

Id          Price           Total that i need     
----------- ------------  ---------
1           500             500
2           200             500+200 = 700
3           NULL            Because price is null put previous value = 700
4           300             SUM of previous row = 500+200+(calc to 700)+ 300 = 1700 
5           NULL            Because price is null put previous value = 1700

Please note that I can't use variable.

Also I am using sub-query, but does not work (only sum of price that is not null returned).

Can sql Update price real-time (record by record like cursor) ?

http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver?lq=1 — Mudassir Hasan, Mar 01 '13 at 05:30
I don't see why for id=4 you calculate total =500+200+(calc to 700)+ 300 but not 500+(calc to 500+200)+(calc to 700)+ 300 — Igor Borisenko, Mar 01 '13 at 06:27
every null price must fill from previous row, therefore first NULL must set to 700 but second NULL must set 500+200+300+(calculated value of first NULL that set to 700) = 1700. — Hamid, Mar 01 '13 at 06:49

score 0 · Answer 1 · edited May 23 '17 at 10:25

0

Actually, this is typical task calculating running totals.

Try this

create table #t(id int, price decimal(10,2), tot_sum decimal(10,2))

insert #t(id, price) values(1, 500)
insert #t(id, price) values(2, 200)
insert #t(id, price) values(3, NULL)
insert #t(id, price) values(4, 300)
insert #t(id, price) values(5, NULL)

update a
set tot_sum=gr.tot_sum
from #t a 
     join(select a.id, max(a.price) as price, sum(isnull(b.price, 0))+max(isnull(a.price,0)) as tot_sum
            from #t a 
                 left join #t b on b.id < a.id
            group by a.id) gr on a.id=gr.id

select * from #t t order by id


drop table #t

edited May 23 '17 at 10:25

Community

1
1

answered Mar 01 '13 at 05:17

Igor Borisenko

3,806
3
34
49

thanks for answer, but this wrong result. first NULL Price calculate to 700 that i right, but next record total must 1400 and not 1000. if price set a same time that update record, my problem will be solved. – Hamid Mar 01 '13 at 05:48

score 0 · Answer 2 · answered Mar 01 '13 at 05:28

You will have to study CTE. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query. A CTE can be used to:

Create a recursive query. For more information, see Recursive Queries Using Common Table Expressions.
Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
Reference the resulting table multiple times in the same statement.

thanks for fast reply, i know about CTE and using it in many causes, but in case, not have idea (performance problem). — Hamid, Mar 01 '13 at 05:52

score 0 · Answer 3 · answered Mar 01 '13 at 18:28

An interesting problem, even if somewhat unusual. Yet it seems solvable. If I am not missing anything, here's how you could go about it.

Calculate running totals in a "normal" way, i.e. ignoring the nulls (in fact, treating them as 0s).
From the previous result set, select only rows where Price is null and calculate running totals of their running totals.
Add the closest preceding "running running total" from the second result set to every running total in the first set.

This is my implementation of the above:

WITH AllTotals AS (
  SELECT
    Id,
    Price,
    Total = SUM(Price) OVER (ORDER BY Id)
  FROM atable
)
, NullTotals AS (
  SELECT
    Id,
    Total = SUM(Total) OVER (ORDER BY Id)
  FROM AllTotals
  WHERE Price IS NULL
)
SELECT
  Id,
  Price,
  Total = Total + COALESCE((
    SELECT TOP (1) Total
    FROM NullTotals
    WHERE NullTotals.Id < AllTotals.Id
    ORDER BY Id DESC
  ), 0)
FROM AllTotals
;

And you can try this query at SQL Fiddle.

T-SQL Update field from previous updated

3 Answers3