Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 278
  • 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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