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 2008 Forums
 Transact-SQL (2008)
 Update Incremental Accumulated Data

Author  Topic 

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-04-13 : 09:44:52
I have data like below in table

ID StartValue
1 0.050
2 0.0056
3 0.556
4 -0.158

I 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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-04-14 : 02:21:34
yes it can be differ in case of deletion.
Go to Top of Page

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

Go to Top of Page

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.

Go to Top of Page

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 cte2
OPTION (MAXRECURSION 0);
Go to Top of Page

keyursoni85
Posting Yak Master

233 Posts

Posted - 2012-04-15 : 14:15:22
Its great!! many thanks to you.

I get it working fine. :)
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-04-15 : 17:56:46
You are quite welcome.)
Go to Top of Page
   

- Advertisement -