Author |
Topic |
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-04-13 : 09:44:52
|
I have data like below in tableID StartValue1 0.0502 0.00563 0.5564 -0.158I want to update above StartValue column row like..It will match next row value and IF Next row value is less than previous row then ADD previous row value into next row value.It will be incremental update.. so once if we updated StartValue then for next row It will check and compare with updated value.. |
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-13 : 10:28:56
|
Is this an explicit update or automated update? if automated (trigger for an example) how you will you prevent it from updating every next value when your table has 1 billion row?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-04-13 : 11:02:27
|
It is explicit..I want to write one query which returns with incremental total as i mentioned above.. if next row value less than previous then add current row + next row .. and then match another next row with that added (incremental) value. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-04-13 : 11:15:55
|
is ID sequential or could the number break because of deletion etc.1,2,4,6,7,8<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-04-14 : 02:21:34
|
yes it can be differ in case of deletion. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-14 : 10:41:42
|
quote: It will match next row value and IF Next row value is less than previous row then ADD previous row value into next row value.
Do you have three rows that need to be considered? NEXT ROW, PREVIOUS ROW, and CURRENT ROW?Can you explain with some sample data that is representative of the various possible combinations - for example something like this:CURRENT ROW NEXT ROW PREVIOUS ROW SET CURRENT ROW TO 1.1 2.1 -- 2.1 2.1 0.7 2.1 0.7 0.7 3.0 0.7 0.7 |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-04-15 : 01:44:48
|
Thanks for reply..No, I want to match current row with next row only and if next row value is less then current row then add current row value to next row value. But then for another match, we need to consider this updated (added) value to match further.Below is sample which I have modified in your given.CURRENT ROW NEXT ROW PREVIOUS ROW SET CURRENT ROW TO 1.1 0.1 -- 1.1 0.1 0.7 1.1 1.2 (1.1 + 0.1 = 1.2)(Row value is less than previous row value) 0.7 1.1 1.2 1.9 (1.2 > 0.7 = 1.9) 1.1 10 1.9 3.0 (1.9 > 1.1 = 3.0) and so on.. just we need to match updated value to next row to update. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-15 : 10:23:32
|
You should be able to use a recursive CTE to do this - like shown below. The code compiles, but I have not tested the logic. Pretty much all the logic of what new value to assign is in the case expression in the recursive part of the CTE shown in red.;WITH cte1 AS( SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RN FROM YourTable),cte2 AS ( SELECT *, startvalue as newValue FROM cte1 WHERE RN = 1 UNION ALL SELECT c1.Id, c1.startValue, c1.RN, CASE WHEN c1.startvalue < c2.newValue THEN c1.startvalue + c2.NewValue ELSE c1.startvalue END FROM cte1 c1 INNER JOIN cte2 c2 ON c1.RN = c2.RN+1)SELECT id, startvalue, newvalue FROM cte2OPTION (MAXRECURSION 0); |
|
|
keyursoni85
Posting Yak Master
233 Posts |
Posted - 2012-04-15 : 14:15:22
|
Its great!! many thanks to you.I get it working fine. :) |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-04-15 : 17:56:46
|
You are quite welcome.) |
|
|
|