sum of this row value plus previous row value..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sum of this row value plus previous row value..

Author

Topic

jitendra.sawant
Starting Member

2 Posts

Posted - 2012-05-07 : 03:29:27

Hi,

i am using a query

select year(a.bod_dt) as year, month(a.bod_dt)as month, Sum(Cast(a.THV AS MONEY)) as ThvSum
from Main_Calc_NEW as a
group by year(a.bod_dt), month(a.bod_dt) order by year(a.bod_dt) , month(a.bod_dt)

and getting a result

year month ThvSum
2010 12 7900785.00
2011 1 220043056.00
2011 2 273445394.00
2011 3 286116658.00
2011 4 293334502.00
2011 5 340162084.00
2011 6 384224662.00
2011 7 643044951.00
2011 8 845888118.00
2011 9 1241187393.00
2011 10 2025064350.00

I want output to be

year month ThvSum Balance
2010 12 7900785 7900785
2011 1 220043056 227943841
2011 2 273445394 501389235
2011 3 286116658 787505893
2011 4 293334502 1080840395
2011 5 340162084 1421002479
2011 6 384224662 1805227141

where 'Balance' values will be sum of this rows ThvSum + previous rows ThvSum..

Thanks in advance..

Sachin.Nand

2937 Posts

Posted - 2012-05-07 : 03:30:56

Just Google "running total in sql server".

After Monday and Tuesday even the calendar says W T F ....

jitendra.sawant
Starting Member

2 Posts

Posted - 2012-05-07 : 05:08:06

Got the solution I did it like this

DECLARE @DateStart date= (select min(convert(datetime,bod_dt,112)) from main_calc_new)

;WITH CTE
AS (SELECT ID = ROW_NUMBER() OVER(ORDER BY year(bod_dt) , month(bod_dt)),
year(bod_dt) as year, month(bod_dt)as month,Sum(Cast(THV AS MONEY)) as THV
FROM main_calc_new
WHERE convert(datetime,bod_dt,112) >= @DateStart group by year(bod_dt) , month(bod_dt))
SELECT [year], [month], [THV],
Balance = (SELECT SUM(THV)
FROM CTE
WHERE ID <= A.ID)
FROM CTE AS A
ORDER BY ID

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-09 : 20:36:21

[code]
SELECT * FROM YOURTABLE X
CROSS APPLY ( SELECT SUM(y.THSUM)AS [RuningTotal] FROM YOURTABLE Y WHERE
Y.THSUM <= X.THSUM)TAB
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-10 : 00:14:27

quote:
Originally posted by jitendra.sawant

Hi,

i am using a query

select year(a.bod_dt) as year, month(a.bod_dt)as month, Sum(Cast(a.THV AS MONEY)) as ThvSum
from Main_Calc_NEW as a
group by year(a.bod_dt), month(a.bod_dt) order by year(a.bod_dt) , month(a.bod_dt)

and getting a result

year month ThvSum
2010 12 7900785.00
2011 1 220043056.00
2011 2 273445394.00
2011 3 286116658.00
2011 4 293334502.00
2011 5 340162084.00
2011 6 384224662.00
2011 7 643044951.00
2011 8 845888118.00
2011 9 1241187393.00
2011 10 2025064350.00

I want output to be

year month ThvSum Balance
2010 12 7900785 7900785
2011 1 220043056 227943841
2011 2 273445394 501389235
2011 3 286116658 787505893
2011 4 293334502 1080840395
2011 5 340162084 1421002479
2011 6 384224662 1805227141

where 'Balance' values will be sum of this rows ThvSum + previous rows ThvSum..

Thanks in advance..

see scenario 1

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources