Solved

Subquery on the same table

Posted on 2013-06-20
2
274 Views
Last Modified: 2013-06-20
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
Comment
Question by:countrymeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39263371
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
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 39263375
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question