Year to Date Balance -- sql server programming

Newman Emanouel
9/18/2007 10:02:00 PM

Roy

What you have suggested doesnt work, for some reason it does not sum the
differenct period balances. All it does is just repeat the rows with the same
amounts. What I need it to do is for the ytd bal of sap period 7 to add
periodBal Period1 + periodBal Period2 + periodBal Period3 + .......
periodBal Period7

Does that make it clearer, the period is the month balance where month 1 is
Jan to Sept being period 9

Regards

[quoted text, click to view]

Roy Harvey (MVP)
9/18/2007 11:43:21 PM

SELECT A.Period,
SUM(B.PeriodBal) as RunningBal
FROM TheTable as A
JOIN TheTable as B
ON A.Account = B.Account
AND A.Period >= B.Period
GROUP BY A.Period

Roy Harvey
Beacon Falls, CT

On Tue, 18 Sep 2007 20:34:01 -0700, Newman Emanouel
[quoted text, click to view]

Adam Machanic
9/18/2007 11:44:00 PM

I have no clue what "period" means, so just a guess:

SELECT SUM(PeriodBal) AS Total
FROM YourTable
WHERE Account = 60001

--

Adam Machanic
SQL Server MVP - http://sqlblog.com

Author, "Expert SQL Server 2005 Development"
http://www.apress.com/book/bookDisplay.html?bID=10220

[quoted text, click to view]

Uri Dimant
9/19/2007 12:00:00 AM

Hi

create table #t (Account int,Period int, PeriodBal decimal(18,3))
insert into #t values (60001, 0, 365.88)
insert into #t values (60001, 1, 348.82)
insert into #t values (60001, 2, 534.55)
insert into #t values (60001, 3, -104.19)
insert into #t values (60001, 4, 958.12)
insert into #t values (60001, 5, -838.91)
insert into #t values (60001, 6, -2440.78)
insert into #t values (60001, 7, 5646.94)

select *,(select #t.PeriodBal+sum(t1.PeriodBal) from #t t1 where
t1.account=#t.account
and t1.period<=#t.period)
from #t

If it does not help please post a desired result

[quoted text, click to view]

Roy Harvey (MVP)
9/19/2007 6:58:45 AM

On Tue, 18 Sep 2007 22:02:00 -0700, Newman Emanouel
[quoted text, click to view]

I might have made a mistake I don't see, or it might have lost
something in translation. If you post the code that you wrote based
on my example maybe I will notice something.

Roy Harvey

Hasan H. TATAR
9/30/2007 11:24:36 PM

It works.

select *,isnull((select #t.PeriodBal+sum(t1.PeriodBal) from #t t1 where
t1.account=#t.account
and t1.period<#t.period),#t.PeriodBal)
from #t

Wed, 19 Sep 2007 09:52:06 +0300 tarihinde Uri Dimant <urid@iscar.co.il>
þöyle yazmýþ:

[quoted text, click to view]

--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Year to Date Balance