How to add the total balances of previous year into the next year?

I'm working with a table called SALFLDGAMS where it has two columns that I need to work with. One of the columns is Period which has years from 2000 to 2005 and the other column is called amount which has the balance for that year. Let me explain in a little more detail. There are account codes associated with the years as well so there will be many places where for example the year 2000 will show up with a given value. What I'm trying to do is to create a view which has a BeginBalance column which adds lets say all the values for 2000 and sticks them to 2001. So what I'm saying is all the values from the previous year I want them in the current year. All in one column. Thanks for the help guys.can you post your table structure, some sample data and the result you want ?|||Ok, I will post the structure of the table and the structure of the view that I want.|||

SALFLDGAMS Table:

Acc_Code Period Amount
11500 2000 234
11511 2000 345
11500 2000 223

NewView:

Acc_Code Period BeginBalance
11500 2001 457

I want a new view based on the SALFLDGAMS table that shows the begin balance for example 2001 that comes from the total amount Sum of 2000.

|||

Hello there:

SELECT Acc_code, CONVERT(int, Period) + 1 AS PeriodNext, SUM(Account) AS PreBalance
FROM dbo.myAccount
GROUP BY Acc_code, Period

|||what happens if I have to keep Period as Varchar? How could it do it then?|||

From reading your other questions about this, you can modify your query to reach your goal.

Suppose your period column is Varchar column in this format: 20020101

SELECT Acc_code, (CONVERT(int, Left(Period,4)) + 1) AS PeriodNext, SUM(Account) AS PreBalance
FROM dbo.myAccount
GROUP BY Acc_code, Period

|||Thanks for the help guys!

How to add a do loop in sql? Thanks a lot!

What I am trying to do is to get balances at each month-end from Jan to
Dec 2004. Now I am doing it by manually changing the date for each
month, but I want to do all the months at one time. Is there a way to
add something like a do loop to achieve that goal? Please see my query
below. Thanks so much!

declare @.month_date_b smalldatetime
--B month beginning date
declare @.month_date_e smalldatetime
--E month ending date

select @.month_date_b='9/1/2004'
select @.month_date_e='9/30/2004'

select a.person_id, a.fn_accno, a.fn_bal, b.mm_open
from fn_mm_fnbal as a
join fn_mm_list as b
on a.person_id=b.person_id
and b.mm_open < @.month_date_e
where a.bal_date between @.month_date_b and @.month_date_e
group by a.person_id, a.fn_accno, a.fn_bal, b.mm_open
order by a.fn_accno, a.fn_balRather than a loop, you can use a join to a set of dates. Or you could
just query the dates out of a Calendar table if you have one. Calendar
tables are very useful for this sort of thing:

SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
FROM fn_mm_fnbal AS A
JOIN fn_mm_list AS B
ON A.person_id = B.person_id
JOIN
(SELECT CAST('20040101' AS DATETIME) UNION ALL
SELECT '20040201' UNION ALL
SELECT '20040301' UNION ALL
SELECT '20040401' UNION ALL
SELECT '20040501' UNION ALL
SELECT '20040601' UNION ALL
SELECT '20040701' UNION ALL
SELECT '20040801' UNION ALL
SELECT '20040901' UNION ALL
SELECT '20041001' UNION ALL
SELECT '20041101' UNION ALL
SELECT '20041201') AS C(cal_date)
ON A.bal_date >= C.cal_date
AND A.bal_date < DATEADD(M,1,C.cal_date)
AND B.mm_open < DATEADD(M,1,C.cal_date)
GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
ORDER BY A.fn_accno, A.fn_bal

--
David Portas
SQL Server MVP
--|||Thanks David! It is a great idea, I forgot to use my calendar table.

David Portas wrote:
> Rather than a loop, you can use a join to a set of dates. Or you
could
> just query the dates out of a Calendar table if you have one.
Calendar
> tables are very useful for this sort of thing:
> SELECT C.cal_date, A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> FROM fn_mm_fnbal AS A
> JOIN fn_mm_list AS B
> ON A.person_id = B.person_id
> JOIN
> (SELECT CAST('20040101' AS DATETIME) UNION ALL
> SELECT '20040201' UNION ALL
> SELECT '20040301' UNION ALL
> SELECT '20040401' UNION ALL
> SELECT '20040501' UNION ALL
> SELECT '20040601' UNION ALL
> SELECT '20040701' UNION ALL
> SELECT '20040801' UNION ALL
> SELECT '20040901' UNION ALL
> SELECT '20041001' UNION ALL
> SELECT '20041101' UNION ALL
> SELECT '20041201') AS C(cal_date)
> ON A.bal_date >= C.cal_date
> AND A.bal_date < DATEADD(M,1,C.cal_date)
> AND B.mm_open < DATEADD(M,1,C.cal_date)
> GROUP BY A.person_id, A.fn_accno, A.fn_bal, B.mm_open
> ORDER BY A.fn_accno, A.fn_bal
> --
> David Portas
> SQL Server MVP
> --