Update the sum using loop - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update the sum using loop

Author

Topic

velkalai
Starting Member

2 Posts

Posted - 2007-08-14 : 00:06:23

Hi, I want to update a column in a table..
The table has multiple clients and each client may have multiple records.. I need to find the sum a column for each client and update a particular column.

update tblA
set tblA.f_totalvalue = (select
sum(case
when f_markvalue = 0 then f_lateValue
when f_markvalue < f_lateValue then f_marketValue
else f_latevalue
end)
from tblA B where B.i_perno = B.i_perno)

Though this sql is not correct, hope it explains the logic..

How to loop through each records and find which value is lower and sum it all together and update a column for each person

mobasha
Starting Member

45 Posts

Posted - 2007-08-14 : 08:21:40

u mean that u need to sum a column for each client and then Decide Wither to update the record or not?

MobashA

velkalai
Starting Member

2 Posts

Posted - 2007-08-15 : 01:40:02

nope..

Well, let me describe it..

table A:
no i_perno sum_value first_val second_val
1 abc12 0 120 12
2 abc12 0 200 300
3 xyz12 0 210 0
4 xyz12 0 100 200

Now..
sum_value = sum(case
when value = 0 then f_lateValue
when first_val< second_val then first_val
else second_val
END)
How to achieve this? any idea???
end)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-15 : 07:59:32

[code]UPDATE a
SET sum_value = s.sum_value
FROM tablea a
INNER JOIN
(
SELECT i_perno, sum_value = SUM(CASE
WHEN value = 0 THEN f_lateValue
WHEN first_val< second_val THEN first_val
ELSE second_val
END)
FROM tablea
GROUP BY i_perno
) s ON a.i_perno = s.i_perno[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources