[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Subquery on the same table

I have a table with four columns

Code
Value
Rank
RollingValue

I want to update the RollingValue to the Value plus the Value in the previous Rank
Initially all the values in the RollingVale are zero

Code   Value      Rank       RollingValue
ABCD   7            1            0
ABCD   21       2            0
ABCD   10            3            0


Code   Value      Rank       RollingValue
ABCD   7            1            7
ABCD   21       2            28
ABCD   10            3            38

I tried this

         UPDATE TableT
        SET RollingValue = t.Value + p.Value  
        FROM  TableT  t
        INNER JOIN
       ( Select Code, Value, Rank from TableT) AS  p        
        ON t.Code = p.Code
        WHERE (t.Rank = (p.Rank + 1))
0
countrymeister
Asked:
countrymeister
  • 2
1 Solution
 
Louis01Commented:
declare @TableT TABLE (Code char(4) not null
                      ,Value int not null
                      ,[Rank] int not null
                      ,RollingValue int not null)

insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 7, 1, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 21, 2, 0)
insert into @TableT (Code,Value,[Rank],RollingValue) values ('ABCD', 10, 3, 0)

update t1
   set t1.RollingValue = (select SUM(t2.Value) 
                            from @TableT t2
                           where t2.[Rank] <= t1.[Rank])
  from @TableT t1

select * from @TableT

Open in new window

0
 
Louis01Commented:
Example of how you'll select it:
select t2.Code
     , t2.Value
     , t2.[Rank]
     , SUM(t1.Value) as RollingValue
  from @TableT t1 right outer join @TableT t2
    on t1.[Rank] <= t2.[Rank]
 group by t2.Code
        , t2.Value
        , t2.[Rank]
 order by t2.[Rank]

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now