accumulate total

[mcnewsxp]

i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

[Erland Sommarskog]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date. how to?

1) CREATE TABLE statements for your tables.
2) INSERT statements with sample data.
3) The desired result given the sample.
4) Which version of SQL Server you are using.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008

income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590

so i'd like to get the income - expense to accumulate in the balance column. balance is currently 0 so the [desired balance] illustrates what I am trying achieve.

[Bob Barrows]

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008
income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590
so i'd like to get the income - expense to accumulate in the balance
column. balance is currently 0 so the [desired balance] illustrates
what I am trying achieve.

You need a column to resolve ties in that date column. Does your table
contain an identity column? Or is there a key guaranteeing one record per
date? (See why we want CREATE TABLE statements? To avoid back-and-forth
questioning like this)

[mcnewsxp]

Post by Bob Barrows

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008
income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590
so i'd like to get the income - expense to accumulate in the balance
column. balance is currently 0 so the [desired balance] illustrates
what I am trying achieve.

You need a column to resolve ties in that date column. Does your table
contain an identity column? Or is there a key guaranteeing one record per
date? (See why we want CREATE TABLE statements? To avoid back-and-forth
questioning like this)

my data sample is a good enough example to work with. but I could have included an [account number] column. yes, there is an identity column called recid.

I figure I can do the rest once I know how to accumulate.

[Bob Barrows]

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008
income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590
so i'd like to get the income - expense to accumulate in the balance
column. balance is currently 0 so the [desired balance] illustrates
what I am trying achieve.

Try this:
WITH t AS (SELECT 200 AS income, 0 AS expense,CAST('20110501' AS DATETIME)
AS entrydate
UNION ALL SELECT 350,0,'20110517'
UNION ALL SELECT 000,100,'20110517'
UNION ALL SELECT 000,110,'20110601'
UNION ALL SELECT 1200,50,'20110615')
, t1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY entrydate) AS row,* FROM t)
SELECT t1.*, (SELECT SUM(income - expense) FROM t1 AS tot WHERE row <=
t1.row) FROM t1

Notice there is no control over which 20110517 record comes first in the
results

[mcnewsxp]

Post by Bob Barrows

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008
income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590
so i'd like to get the income - expense to accumulate in the balance
column. balance is currently 0 so the [desired balance] illustrates
what I am trying achieve.

WITH t AS (SELECT 200 AS income, 0 AS expense,CAST('20110501' AS DATETIME)
AS entrydate
UNION ALL SELECT 350,0,'20110517'
UNION ALL SELECT 000,100,'20110517'
UNION ALL SELECT 000,110,'20110601'
UNION ALL SELECT 1200,50,'20110615')
, t1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY entrydate) AS row,* FROM t)
SELECT t1.*, (SELECT SUM(income - expense) FROM t1 AS tot WHERE row <=
t1.row) FROM t1
Notice there is no control over which 20110517 record comes first in the
results

bob - why would want to use literals?

[Gert-Jan Strik]

Post by mcnewsxp

Post by Bob Barrows

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

mssql 2008
income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590
so i'd like to get the income - expense to accumulate in the balance
column. balance is currently 0 so the [desired balance] illustrates
what I am trying achieve.

WITH t AS (SELECT 200 AS income, 0 AS expense,CAST('20110501' AS DATETIME)
AS entrydate
UNION ALL SELECT 350,0,'20110517'
UNION ALL SELECT 000,100,'20110517'
UNION ALL SELECT 000,110,'20110601'
UNION ALL SELECT 1200,50,'20110615')
, t1 AS(
SELECT ROW_NUMBER() OVER (ORDER BY entrydate) AS row,* FROM t)
SELECT t1.*, (SELECT SUM(income - expense) FROM t1 AS tot WHERE row <=
t1.row) FROM t1
Notice there is no control over which 20110517 record comes first in the
results

bob - why would want to use literals?

The literal's in Bob's post are only to simulate a virtual table. If you had
provided CREATE TABLE and INSERT statements, I am sure Bob would have used them.

So your query would typically not have the WITH t AS (..) , but instead "t"
represents your table.
--
Gert-Jan

[Erland Sommarskog]

Post by mcnewsxp
bob - why would want to use literals?

Bob was kind to post the data you were too lazy to post yourself.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

what is missing from?

income expense [entry date] balance [desired balance]
200 0 1.13.2013 0 200
350 0 1.17.2013 0 550
0 110 2.11.2013 0 440
1200 50 2.21.2013 0 1590

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

i see that the code bob posted renders the results i am looking for. how would i code it with an existing table called 'mytable' using the same column names?

[Erland Sommarskog]

Post by mcnewsxp
i see that the code bob posted renders the results i am looking for.
how would i code it with an existing table called 'mytable' using the
same column names?

Bob showed you a technique. Now you go home and pick his query apart to
learn how it works, so you can use it in your table. You come here ask
questions to learn, don't you?

His first CTE is your table; he just composed it on the fly.

To update your table, make the last query yet CTE, call it t2 and you can
do:

UPDATE t2
SET balance = accumulated_total

Yes, I put as much effort into my answers as you put into your questions.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

how does the UPDATE of [balance] occur?

[Bob Barrows]

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

how does the UPDATE of [balance] occur?

Incidentally, the term for the value you wish to calculate is "running
total".

This is the first time you asked about updatig a column. Why would you even
think of storing a calculated value? Don't go down that road. Create a view
that calculates the value using the technique I showed in my earlier reply.
Then just query that view when you want to see the running totals.

Since your source table has an as-yet-to-be named identity column, you do
not have to use the ROW_NUMBER() function to break ties between dates. My
example given the existnce of the identity column would change to:


SELECT t1.*, (SELECT SUM(income - expense) FROM yourtable AS tot WHERE
identitycol <=
t1.identitycol) FROM yourtable as t1

[Erland Sommarskog]

Post by Bob Barrows
This is the first time you asked about updatig a column. Why would you
even think of storing a calculated value? Don't go down that road.
Create a view that calculates the value using the technique I showed in
my earlier reply.

Here I will have to disagree. If there are many transactions, computing the
balance on the fly is not on an option on SQL 2008 where about all solutions
are proportional to the square of the number of transactions. (It's possible
to get linear performance, but that requires the CLR.)

But not even on SQL 2012 where the whole thing is easier to write with
linear performance, it's not a good idea, if there can be tens of thousands
of transactions for an account.

[Bob Barrows]

Post by Erland Sommarskog

Post by Bob Barrows
This is the first time you asked about updatig a column. Why would
you even think of storing a calculated value? Don't go down that
road. Create a view that calculates the value using the technique I
showed in my earlier reply.

Here I will have to disagree. If there are many transactions,
computing the balance on the fly is not on an option on SQL 2008
where about all solutions are proportional to the square of the
number of transactions. (It's possible to get linear performance, but
that requires the CLR.)
But not even on SQL 2012 where the whole thing is easier to write with
linear performance, it's not a good idea, if there can be tens of
thousands of transactions for an account.

Yes, I should have clarified - one of the reasons for violating the rule
against storing calculations is if doing the calculation creates too much of
a performance impact.
Other reasons include the need to store historical calculations, i.e. those
depend on values that were current at the time of doing the transaction.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

Erland, if you asked me which way to the Systembolaget I would simply tell you.

[Bob Barrows]

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

Erland, if you asked me which way to the Systembolaget I would simply tell you.

So would he, I am sure, but that is because I think Systembolaget is a
clearly-defined, unambiguous location, neither of which adjective describes
your question here.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

seriously?

I have a table that has an Income and Expense columns. I have added a Balance column. I want to update the Balance column with result of Income - Expense. The tricky part is that the Balance column must accumulate using either AccountingEntryID column order or the EntryDate column order. the example data I included is pretty strait forward.

CREATE TABLE [dbo].[AccountEntry](
[AccountEntryID] [int] IDENTITY(1,1) NOT NULL,
[Income] [money] NULL,
[Expense] [money] NULL,
[EntryDate] [datetime] NULL,
[Balance] [money] NULL
) ON [PRIMARY]
GO

1 300.0000 0.0000 2013-02-02 00:00:00.000 NULL
2 250.0000 0.0000 2013-02-21 00:00:00.000 NULL
3 0.0000 75.0000 2013-03-01 00:00:00.000 NULL
4 1000.0000 87.0000 2013-03-12 00:00:00.000 NULL
5 167.0000 0.0000 2013-03-19 00:00:00.000 NULL

[Bob Barrows]

Post by mcnewsxp

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in
sequential order by entry date.
how to?
tia,
mcnewsw8

seriously?
I have a table that has an Income and Expense columns. I have added
a Balance column. I want to update the Balance column with result of
Income - Expense. The tricky part is that the Balance column must
accumulate using either AccountingEntryID column order or the
EntryDate column order. the example data I included is pretty strait
forward.
CREATE TABLE [dbo].[AccountEntry](

<snip>

Much better, but it's a little late, isn't it? You have what you need
already, don't you? If you had provided this information from the start, you
would have had a working solution two days ago.

[Erland Sommarskog]

Post by mcnewsxp
I have a table that has an Income and Expense columns. I have added a
Balance column. I want to update the Balance column with result of
Income - Expense. The tricky part is that the Balance column must
accumulate using either AccountingEntryID column order or the EntryDate
column order. the example data I included is pretty strait forward.
CREATE TABLE [dbo].[AccountEntry](
[AccountEntryID] [int] IDENTITY(1,1) NOT NULL,
[Income] [money] NULL,
[Expense] [money] NULL,
[EntryDate] [datetime] NULL,
[Balance] [money] NULL
) ON [PRIMARY]
GO

UPDATE AccountEntry
SET Balance = (SELECT SUM(b.Income - b.Expense)
FROM AccountEntry b
WHERE b.AcountEntryID <= a.AccountEntryID)
FROM AccountEntry b

You will need to figure out what applies if any of Income and Expense are
NULL.

And, yeah, I ignored EntryDate. Supposedly transactions are entered in
order, so AccountEntry should be all you need. On top of that EntryDate
is nullable - how would those rows be handled. Yes, you can get EntryDate
in there:

UPDATE AccountEntry
SET Balance = (SELECT SUM(b.Income - b.Expense)
FROM AccountEntry b
WHERE (b.EntryDate <= a.EntryDate OR
b.EntryDate = a.EntryDate AND
b.AcountEntryID <= a.AccountEntryID))
FROM AccountEntry b

But performance will be horrendeous, and you will lose the rows with
NULL in them.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

i have half of what i need.
plus some stuff i don't.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

erland - that is fantastic. thanks much.

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

where does a.AccountEntryID come from?

[mcnewsxp]

Post by mcnewsxp
i need to accumulate income and expenses into a balance column in sequential order by entry date.
how to?
tia,
mcnewsw8

you meant 'FROM AccountingEntry a'
works great.
thanks much!