How to get cumulative balance units.

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)

How to get cumulative balance units.

Author

Topic

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-08-17 : 10:00:52

Hi all,

following are my table data


Date	Units
27/10/2008	3450.9959
03/11/2008	4.3751
24/11/2008	7.0939
01/12/2008	5.3232
08/12/2008	9.1741
15/12/2008	7.3174
22/12/2008	5.657
29/12/2008	5.0967
05/01/2009	5.5539
12/01/2009	4.9322
13/01/2009	980.5652
19/01/2009	1.3394
19/01/2009	4.7882
27/01/2009	4.7935
27/01/2009	1.3372
02/02/2009	1.3944
02/02/2009	5.0123
09/02/2009	5.4741
09/02/2009	1.5185
16/02/2009	1.2062
16/02/2009	4.3616

and i want output like this



Date	Units	Balance Units
27/10/2008	3450.9959	3450.9959
03/11/2008	4.3751	3455.371
24/11/2008	7.0939	3462.4649
01/12/2008	5.3232	3467.7881
08/12/2008	9.1741	3476.9622
15/12/2008	7.3174	3484.2796
22/12/2008	5.657	3489.9366
29/12/2008	5.0967	3495.0333
05/01/2009	5.5539	3500.5872
12/01/2009	4.9322	3505.5194
13/01/2009	980.5652	4486.0846
19/01/2009	1.3394	4487.424
19/01/2009	4.7882	4492.2122
27/01/2009	4.7935	4497.0057
27/01/2009	1.3372	4498.3429
02/02/2009	1.3944	4499.7373
02/02/2009	5.0123	4504.7496
09/02/2009	5.4741	4510.2237
09/02/2009	1.5185	4511.7422
16/02/2009	1.2062	4512.9484
16/02/2009	4.3616	4517.31

balance unit = sum(units) [all previous units] + current units

please help me

Pankaj

Sachin.Nand

2937 Posts

Posted - 2009-08-17 : 10:04:11

Have a look at this
http://www.sqlteam.com/article/calculating-running-totals

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 11:18:02

[code]
declare @sample table
(
[Date] datetime,
[Units] decimal(10,4)
)
set dateformat dmy
insert into @sample
select '27/10/2008', 3450.9959 union all
select '03/11/2008', 4.3751 union all
select '24/11/2008', 7.0939 union all
select '01/12/2008', 5.3232 union all
select '08/12/2008', 9.1741 union all
select '15/12/2008', 7.3174 union all
select '22/12/2008', 5.6570 union all
select '29/12/2008', 5.0967 union all
select '05/01/2009', 5.5539 union all
select '12/01/2009', 4.9322 union all
select '13/01/2009', 980.5652 union all
select '19/01/2009', 1.3394 union all
select '19/01/2009', 4.7882 union all
select '27/01/2009', 4.7935 union all
select '27/01/2009', 1.3372 union all
select '02/02/2009', 1.3944 union all
select '02/02/2009', 5.0123 union all
select '09/02/2009', 5.4741 union all
select '09/02/2009', 1.5185 union all
select '16/02/2009', 1.2062 union all
select '16/02/2009', 4.3616

select *,
[Balance Units] = (select sum(Units) from @sample x where x.[Date] <= s.[Date])
from @sample s
order by s.[Date]

select *
from @sample s
cross apply
(
select [Balance Units] = sum(Units)
from @sample x
where x.[Date] <= s.[Date]
) b
order by s.[Date]

/*
Date Units Balance Units
------------------------------------------------------ ------------ ----------------------------------------
2008-10-27 00:00:00.000 3450.9959 3450.9959
2008-11-03 00:00:00.000 4.3751 3455.3710
2008-11-24 00:00:00.000 7.0939 3462.4649
2008-12-01 00:00:00.000 5.3232 3467.7881
2008-12-08 00:00:00.000 9.1741 3476.9622
2008-12-15 00:00:00.000 7.3174 3484.2796
2008-12-22 00:00:00.000 5.6570 3489.9366
2008-12-29 00:00:00.000 5.0967 3495.0333
2009-01-05 00:00:00.000 5.5539 3500.5872
2009-01-12 00:00:00.000 4.9322 3505.5194
2009-01-13 00:00:00.000 980.5652 4486.0846
2009-01-19 00:00:00.000 1.3394 4492.2122
2009-01-19 00:00:00.000 4.7882 4492.2122
2009-01-27 00:00:00.000 4.7935 4498.3429
2009-01-27 00:00:00.000 1.3372 4498.3429
2009-02-02 00:00:00.000 1.3944 4504.7496
2009-02-02 00:00:00.000 5.0123 4504.7496
2009-02-09 00:00:00.000 5.4741 4511.7422
2009-02-09 00:00:00.000 1.5185 4511.7422
2009-02-16 00:00:00.000 1.2062 4517.3100
2009-02-16 00:00:00.000 4.3616 4517.3100

(21 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-17 : 11:20:32

also see . .
http://weblogs.sqlteam.com/mladenp/archive/2009/07/28/SQL-Server-2005-Fast-Running-Totals.aspx

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-18 : 02:43:31

Also refer point 4
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-18 : 05:31:29

Hi, try this also
this is another way
select identity(int,1,1) as id ,* into #temp from @tab

select amt,date,( select sum(amt) from #temp where id <= t.id )as total from #temp t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources