Adding values in each row - 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 2008 Forums

Transact-SQL (2008)

Adding values in each row

Author

Topic

munya
Starting Member

6 Posts

Posted - 2012-07-20 : 04:44:10

I have a list of customers with the different amounts in different dates. I want to add up amounts to get balances at different dates based on customer number.As an example,I have filled Balance Field with needed values which are sums of amount values for customer number 1.
How can I do that for other customer numbers using SQL server?

My table structure and data looks like this:
CustomerNumber Date amount Balance
1 1/1/2012 15,000 15,000
1 1/2/2012 4,000 19,000
1 1/3/2012 5,000 24,000
1 1/4/2012 60,000 84,000
2 1/1/2012 15,000
2 1/2/2012 4,000
2 1/3/2012 5,000
2 1/4/2012 60,000
3 1/1/2012 15,000
3 1/2/2012 4,000
3 1/3/2012 5,000
3 1/4/2012 60,000

I will appreciate your assistance on this.

jleitao
Posting Yak Master

100 Posts

Posted - 2012-07-20 : 05:22:23

select
CustomerNumber, Date, amount,
(SELECT SUM(amount) FROM Custumers A WHERE A.CustomerNumber=C.CustomerNumber and a.Date <= c.Date) balance
from Custumers C

------------------------
PS - Sorry my bad english

stepson
Aged Yak Warrior

545 Posts

Posted - 2012-07-20 : 05:22:52

select X.* ,y.balance from YOUR_TABLE X
outer apply
(
select sum(amount) as balance
from YOUR_TABLE
where CustomerNumber=X.customerNumber
and date<=X.date) Y

or you can use row_number ()

or Quirky Update : http://www.sqlservercentral.com/articles/T-SQL/68467/

S

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2012-07-20 : 05:26:58

CREATE TABLE #Cust (CustomerNumber INT, PayDate DATETIME, amount INT, Balance INT)

INSERT INTO #Cust
SELECT 1, '1/1/2012', 15000, NULL UNION ALL
SELECT 1, '1/2/2012', 4000, NULL UNION ALL
SELECT 1, '1/3/2012', 5000, NULL UNION ALL
SELECT 1, '1/4/2012', 60000, NULL UNION ALL
SELECT 2, '1/1/2012', 15000, NULL UNION ALL
SELECT 2, '1/2/2012', 4000, NULL UNION ALL
SELECT 2, '1/3/2012', 5000 , NULL UNION ALL
SELECT 2, '1/4/2012', 60000, NULL UNION ALL
SELECT 3, '1/1/2012', 15000, NULL UNION ALL
SELECT 3, '1/2/2012', 4000, NULL UNION ALL
SELECT 3, '1/3/2012', 5000 , NULL UNION ALL
SELECT 3, '1/4/2012', 60000, NULL

SELECT CustomerNumber,PayDate,amount,
ISNULL(amount + ( SELECT
SUM(amount)
FROM #Cust
WHERE PayDate < OuterTable.PayDate
AND CustomerNumber = OuterTable.CustomerNumber

),amount) AS Balance
FROM #Cust OuterTable
ORDER BY CustomerNumber,PayDate

DROP TABLE #Cust

--------------------------
http://connectsql.blogspot.com/

munya
Starting Member

6 Posts

Posted - 2012-07-20 : 07:15:51

Thx for the prompt reply. And it worked perfectly.
I am new on this site but seems I got the right place for SQL discussions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources