2

Possible Duplicate:
Calculate a Running Total in SqlServer

I need to use values from previous row inorder to generate a cumulative value as shown below. Always for each Code for the year 2000 the starting Base is 100. I need to ahieve this using tsql code.

id                Code              Yr             Rate           Base        

1                   4               2000           5              100                                  
2                   4               2001           7              107 (100+7)             
3                   4               2002           4              111 (107+4)                              
4                   4               2003           8              119 (111+8)
5                   4               2004           10             129 (119+10)
6                   5               2000           2              100
7                   5               2001           3              103 (100+3)
8                   5               2002           8              111 (103+8)
9                   5               2003           5              116 (111+5)
10                  5               2004           4              120 (116+4)
Community
  • 1
  • 1
user1210891
  • 63
  • 1
  • 2
  • 6

1 Answers1

0

OK. We have table like this CREATE Table MyTbl(id INT PRIMARY KEY IDENTITY(1,1), Code INT, Yr INT, Rate INT) And we would like to calculate cumulative value by Code. So we can use query like this:

1) recursion (requires more resources, but outputs the result as in the example)

with cte as
(SELECT *, ROW_NUMBER()OVER(PARTITION BY Code ORDER BY Yr ASC) rn
FROM MyTbl),

recursion as
(SELECT id,Code,Yr,Rate,rn, CAST(NULL as int) as Tmp_base, CAST('100' as varchar(25)) AS Base FROM cte
WHERE rn=1
UNION ALL
SELECT cte.id,cte.Code,cte.Yr,cte.Rate,cte.rn, 
CAST(recursion.Base as int),
CAST(recursion.Base+cte.Rate as varchar(25))
FROM recursion JOIN cte ON recursion.Code=cte.Code AND recursion.rn+1=cte.rn
)

SELECT id,Code,Yr,Rate, 
CAST(Base as varchar(10))+ISNULL(' ('+ CAST(Tmp_base as varchar(10))+'+'+CAST(Rate as varchar(10))+')','') AS Base 
FROM recursion
ORDER BY 1


OPTION(MAXRECURSION 0)

2) or we can use a faster query without using recursion. but the result is impossible to generate the strings like '107 (100+7)' (only strings like '107')

SELECT *,

    100 +
    (SELECT ISNULL(SUM(rate),0) /*we need to calculate only the sum in subquery*/
    FROM MyTbl AS a
    WHERE
        a.Code=b.Code /*the year in subquery equals the year in main query*/
        AND a.Yr<b.Yr /*main feature in our subquery*/
        ) AS base

FROM MyTbl AS b
Dima Railguner
  • 173
  • 5
  • 15
  • The rate must be 100 for the first year of each particular code. Also the subsequent Base values must be previous Base value plus the corresponding current Rate. – user1210891 Sep 22 '12 at 19:38
  • Thanks both queries work. How can l update the field Base in the database? – user1210891 Sep 22 '12 at 22:58
  • For first query Just replace SELECT id,Code,Yr,Rate, CAST(Base as varchar(10))+ISNULL(' ('+ CAST(Tmp_base as varchar(10))+'+'+CAST(Rate as varchar(10))+')','') AS Base FROM recursion ORDER BY 1 OPTION(MAXRECURSION 0) to UPDATE MyTbl SET Base = (SELECT CAST(Base as varchar(10))+ISNULL(' ('+ CAST(Tmp_base as varchar(10))+'+'+CAST(Rate as varchar(10))+')','') AS NewBase FROM recursion WHERE MyTbl.id=recursion.id ) OPTION(MAXRECURSION 0) – Dima Railguner Sep 22 '12 at 23:31
  • second query (full): UPDATE MyTbl SET Base = ( SELECT 100 + (SELECT ISNULL(SUM(rate),0) FROM MyTbl AS a WHERE a.Code=b.Code AND a.Yr – Dima Railguner Sep 22 '12 at 23:34
  • Once again thanks very much Dima. One observation though the second query is adding the previous Rate instead of using the next Rate to get the Base. The first query does the job. – user1210891 Sep 23 '12 at 00:05
  • No. It works too. You just must to replace all code after select statement (line №15) to this code : UPDATE MyTbl SET Base = (SELECT CAST(Base as varchar(10))+ISNULL(' ('+ CAST(Tmp_base as varchar(10))+'+'+CAST(Rate as varchar(10))+')','') AS NewBase FROM recursion WHERE MyTbl.id=recursion.id ) OPTION(MAXRECURSION 0) – Dima Railguner Sep 23 '12 at 07:22
  • but anyway better to use second query because it works faster – Dima Railguner Sep 23 '12 at 07:23