Give me Query syntax or any other solution

• ECN INdia

  SSC Enthusiast

  Points: 106

  More actions

  February 16, 2006 at 3:27 am

  #96601

  table has a field 'A' containing numeric value...

  Want a field 'B' to display to cumulative data of row 'A'

  where ,

  B1=A1, B2=B1+A2 , B3=B2+A3 .....

  Here 'B' is not a table field..

  it is just for display purpous on Report..

  ......................................................................................

  column 'A' has following values in desc order : 5,5,4,4,4,3,3,2,2,1

  So 'B' should have 5,10,14,18,22,25,28,30,32,33

  .......................................................................................

  i m working on Ms SQL server 2005 reporting services ..

• Kristipati Subramanyam

  Grasshopper

  Points: 16

  More actions

  February 19, 2006 at 10:42 pm

  #621930

  Hi ,

  I am Kristipati Subramanyam, giving you an example..

  -- =============================================

  -- Create Table

  -- =============================================

  create table TestTbl100( a numeric)

  -- =============================================

  -- insert few records into TestTbl100

  -- =============================================

  insert into TestTbl100 values(5)

  insert into TestTbl100 values(5)

  insert into TestTbl100 values(4)

  insert into TestTbl100 values(4)

  insert into TestTbl100 values(4)

  insert into TestTbl100 values(3)

  insert into TestTbl100 values(3)

  insert into TestTbl100 values(2)

  insert into TestTbl100 values(2)

  insert into TestTbl100 values(3)

  -- =============================================

  -- Create table function (TF)

  -- =============================================

  create function fnTestTbl100() returns @TestTbl100 Table(a numeric, b numeric)

  begin

   declare @a numeric, @b-2 numeric

   set @b-2 = 0

   declare Cur cursor for select a from TestTbl100

   open Cur

   fetch Next from Cur into @a

   while @@Fetch_Status=0

   begin

    set @b-2 = @b-2 + @a

    insert into @TestTbl100 values(@a,@b)

    fetch Next from Cur into @a

   end

   close Cur

   deallocate Cur

   return

  end

  -- =============================================

  -- Select values from fnTestTbl100()

  -- =============================================

  select * from fnTestTbl100() dt