Is it posible in sqlserver 2000

[muddasirmunir]

is it possible to make a running field total in sqlserver 2000 views
and if so how?
can any body answer me
thanks

[jamesd0142]

I don't understand what your asking.
Could you explain in more detail what you require?

[muddasirmunir]

i mean that i want a ruuning total field in sql server 2000 just as we can make a runnig total field in crytal report . for e-g

Sale----------------Balance

100-----------------100

50-------------------150

100------------------250

25-------------------275

here sale colums is the database field which actually stored data and i want to add a balance field which add current record data plus the previous balance

how can i do this in views

I don't understand what your asking.
Could you explain in more detail what you require?

[Jim Doherty]

i mean that i want a ruuning total field in sql server 2000 just as we can make a runnig total field in crytal report . for e-g

Sale----------------Balance

100-----------------100

50-------------------150

100------------------250

25-------------------275

here sale colums is the database field which actually stored data and i want to add a balance field which add current record data plus the previous balance

how can i do this in views

Provided you have a field in the resultset to determine hierarchy ie something other than just the sale amount lets say an unique numeric ID field in a table called tblSale (needed to determine the row) then the following SQL will return your requirement

 
SELECT Sale,
             (SELECT     SUM(Sale)
             FROM         tblSale a
             WHERE     id <= tblSale.id) AS Balance
FROM    tblSale
 

Regards

Jim :)

[jamesd0142]

declare @a int
declare @b int
declare @counter int
declare @counter2 int
declare @count int
    set @count = (select Count(*) from james)
    set @counter = 0
    set @a = (select top 1 (col) from james)
    set @b = (select col01 from james where col = @a) 
if @a = 1
    begin
        set @counter = (@counter + @b)
        update james
        set col02 = @counter where col = @a
    end
 
if @a > 0
begin
 
        while @a < @count
        begin
        set @counter = (select col02 from james where col = @a)
        set @a = @a + 1
 
        set @counter2 = @counter + (select col01 from james where col = @a)
        print @counter2
 
        update james
        set col02 = @counter2 where col = @a
 
        end
end
 

Table name = james
Columns = (col, col01, col02)

Table looks like:
col col01 col02
1 50 <50>
2 100 <150>
3 150 <300>
4 200 <500>
5 250 <750>
6 300 <1050>

-----------------------------------------------------------------------
Theres prob an easier way, but this def works.
-----------------------------------------------------------------------

[muddasirmunir]

thanks james
it is working fine but i it is calculat by updating table can 1 make
a view
for e-g my talbe looks like this (only two fields)

col col01
1 50
2 100
3 150
4 200
5 250
6 300

but my view looks like this (three columns)
Table looks like:
col col01 col02
1 50 <50>
2 100 <150>
3 150 <300>
4 200 <500>
5 250 <750>
6 300 <1050>

thanks in advance

declare @a int
declare @b int
declare @counter int
declare @counter2 int
declare @count int
set @count = (select Count(*) from james)
set @counter = 0
set @a = (select top 1 (col) from james)
set @b = (select col01 from james where col = @a) 
if @a = 1
begin
set @counter = (@counter + @b)
update james
set col02 = @counter where col = @a
end
 
if @a > 0
begin
 
while @a < @count
begin
set @counter = (select col02 from james where col = @a)
set @a = @a + 1
 
set @counter2 = @counter + (select col01 from james where col = @a)
print @counter2
 
update james
set col02 = @counter2 where col = @a
 
end
end
 

Table name = james
Columns = (col, col01, col02)

Table looks like:
col col01 col02
1 50 <50>
2 100 <150>
3 150 <300>
4 200 <500>
5 250 <750>
6 300 <1050>

-----------------------------------------------------------------------
Theres prob an easier way, but this def works.
-----------------------------------------------------------------------

[jamesd0142]

if your view has the three columns then instead of refrencing the table in the code i supplied, can you not simply reference the view you created?

sorry, i'm unable to test this myself as im on a machine that doesnt have sql server installed at present.

mayb you could make a copy of the existing table also and update the copy instead of messing up the origional???

thanks james
it is working fine but i it is calculat by updating table can 1 make
a view
thanks in advance