SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update Incremental Accumulated Data
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

keyursoni85
Posting Yak Master

India
233 Posts

Posted - 04/13/2012 :  09:44:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 04/13/2012 :  10:28:56  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

India
233 Posts

Posted - 04/13/2012 :  11:02:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
1635 Posts

Posted - 04/13/2012 :  11:15:55  Show Profile  Click to see yosiasz's MSN Messenger address  Reply with Quote
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

India
233 Posts

Posted - 04/14/2012 :  02:21:34  Show Profile  Reply with Quote
yes it can be differ in case of deletion.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/14/2012 :  10:41:42  Show Profile  Reply with Quote
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

India
233 Posts

Posted - 04/15/2012 :  01:44:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/15/2012 :  10:23:32  Show Profile  Reply with Quote
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

India
233 Posts

Posted - 04/15/2012 :  14:15:22  Show Profile  Reply with Quote
Its great!! many thanks to you.

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

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 04/15/2012 :  17:56:46  Show Profile  Reply with Quote
You are quite welcome.)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000