Solved

Subquery on the same table

Posted on 2013-06-20
2
273 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
  • 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Resume 5 46
Need help with a query 14 39
compare date to getdate() 8 17
SQL Convert "vertical" data to "horizontal" data 4 30
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.

733 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