SQL Querry - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

SQL Querry

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2006-03-08 : 23:58:23

this is probably an easy one for the experienced out there... and maybe i shouldnt be lazy either buut i have spent reasonable hours trying to solve this querry and i cant get my head around it.

say there is a table.

#Table1 (id,date,amount,desc)
-----------------------------
1 , '01/8/2006' , 1, ' xy '
2 , '01/9/2006' , 4, ' abc '
3 , '01/10/2006' , 6, ' abc '
4 , '01/11/2006' , 10, ' xyz '
5 , '01/13/2006' , 133, ' xyz '
6 , '01/14/2006' , 12, ' abcd '
------------------------------

what i want to do is when i do my querry i should be able to display the sum of the amount
for all the dates less than or equal to that date

so i shoould have

ID Date Amount desc Balance
-------------------------------
1 , '01/8/2006' , 1, ' xy ',1
2 , '01/9/2006' , 4, ' abc ',5
3 , '01/10/2006' , 6, ' abc ',11
4 , '01/11/2006' , -10, ' xyz ',1
5 , '01/13/2006' , 133, ' xyz ',134
6 , '01/14/2006' , -12, ' abcd ',123
---------------------------------

if someone can solve this .... that will be awesome! thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-09 : 00:04:34

[code]select id, date, amount, desc,
(select sum(amount) from table1 x where x.date <= t.date) as balance
from table1 t[/code]

----------------------------------
'KH'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-09 : 01:18:29

Where do you want to show the data?
If you use Reports, then make use of it's Running Total Feature

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources