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]

mcnewsxp (mcou...@mindspring.com) writes:
> 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.


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[mcnewsxp]

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]

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)

[Bob Barrows]

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]

bob - why would want to use literals?

[mcnewsxp]

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.

[Gert-Jan Strik]

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]

mcnewsxp (mcou...@mindspring.com) writes:
> bob - why would want to use literals?

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

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

what is missing from?

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

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?

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

how does the UPDATE of [balance] occur?

[Erland Sommarskog]

mcnewsxp (mcou...@mindspring.com) writes:
> 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.

[Bob Barrows]

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

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

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

[Bob Barrows]

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]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

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]

mcnewsxp wrote:
> On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:
>> 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.

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

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

[Erland Sommarskog]

Bob Barrows (reb0...@NOyahooSPAM.com) writes:
> 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.

[Erland Sommarskog]

mcnewsxp (mcou...@mindspring.com) writes:
> 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]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

erland - that is fantastic. thanks much.

[Bob Barrows]

Erland Sommarskog wrote:
> Bob Barrows (reb0...@NOyahooSPAM.com) writes:
>> 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]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

where does a.AccountEntryID come from?

[mcnewsxp]

On Monday, March 18, 2013 6:24:43 PM UTC-4, mcnewsxp wrote:

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