February 1, 2013 at 10:55 pm
50 ------ 1500----- 1550
100----- - 1550----- 1650
40----- - 1650----- 1690
30----- - 1690----- 1720
Now I do have the value of the 'column1' and I need to generate the values for the 'column2' and 'result'
I do have the initial value of the 'column2' (1500).
I need to add 'column1' to 'column2' and generate the 'result'.
Then I put the value of the 'result' from first row to the second row of the 'column2', after that I add the second value from 'column1' and so I generate the second value for the 'result' and so on.
I am new in SQL server and I am running out of ideas for this complicated problem.
I would appreciate any sugestion
Thanks in advance.
[/center]
February 4, 2013 at 8:06 am
Hi and welcome to SSC. What you are describing is a running total. Here is an article that explains one way of doing this.
http://www.sqlservercentral.com/articles/68467/[/url]
If you find that you need more specific help with coding please first take the time to read the article at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 5, 2013 at 7:06 am
Maybe you could use the sql statement below:
update tablename set column3 = column1 + column2
February 5, 2013 at 7:25 am
HildaJ (2/5/2013)
Maybe you could use the sql statement below:update tablename set column3 = column1 + column2
That doesn't accomplish the running total like the OP is looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2013 at 10:58 pm
Hi angjoni,
You can use below code to perform this operation:
create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)
declare @col2 int = 1500
select * from test99
update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99
select * from test99
drop table test99
February 6, 2013 at 11:30 pm
ashishjain (2/6/2013)
Hi angjoni,You can use below code to perform this operation:
create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)
declare @col2 int = 1500
select * from test99
update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99
select * from test99
drop table test99
This is the quirky update method. Very fast indeed, but you must adhere to some rules to garantuee consistent behaviour. The article Sean linked to has a very good description of the method and its rules.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
February 7, 2013 at 8:11 pm
ashishjain (2/6/2013)
Hi angjoni,You can use below code to perform this operation:
create table test99 (column1 int, column2 int, result int)
insert into test99
values
(50,1500,0),
(100,0,0),
(40,0,0),
(30,0,0)
declare @col2 int = 1500
select * from test99
update test99
set @col2 = result = @col2 + column1,
column2 = @col2
from test99
select * from test99
drop table test99
I'll add an explicit warning to what Koen said:
While it may work in the example case, the above code violates at least 3-4 of the rules in that article.
I won't tell you what they are because you need to read that article and understand the rules before you use this approach in a production system.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply