Tags: 8ed6c8f0, _nextpart_000_004d_01c3fc06, accumulative, charset, content-type, format, iso-8859-1, message, mime, multi-part, plain, programming, running, select, sql, statement, text, value

running accumulative value in select statement

On Programmer » SQL

5,351 words with 4 Comments; publish: Thu, 29 May 2008 04:48:00 GMT; (20062.99, « »)

This is a multi-part message in MIME format.

--=_NextPart_000_004D_01C3FC06.8ED6C8F0

Content-Type: text/plain;

charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable

Hi,

I need to format a report in following layout:

Day Withdrawal Deposit Balance *** ********** ******* *******

1/1 1000

1/4 -200 ' 800

1/5 +500 ' 1300 1/6 -250 ' 1050

SELECT date, (SELECT SUM(withdrawal) from withdrawal w where = w.date=3Dt.date) as withdrawal_amt,

(SELECT SUM(deposit) FROM deposit d WHERE d.date=3Dt.date) as = deposit_amt, 0, 'transaction' as type

FROM trans t

where year(t.date)=3D2004 and month(t.date)=3D1

UNION

SELECT date, 0, 0, open_amt, 'opening' as type

FROM mth_balance b WHERE b.mth=3D 1 and b.year=3D2004

I'm able to compute the daily withdrawal and deposit amount. But who do = I get the running end day total?

It's like waterfall effect, previous end-day balance =3D next-day = opening + (withdrawal + deposit). How will the select SQL like?

Thx in advance.

--=_NextPart_000_004D_01C3FC06.8ED6C8F0

Content-Type: text/html;

charset="iso-8859-1"

Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

&

Hi,

I need to format a report in = following layout:

Day Withdrawal Deposit Balance

*** ********** ******* *******

1/1 = = 1000

1/4 -200 &nb= sp; &nbs= p; ' 800

1/5 &= nbsp; +500 ' 1300 &nb= sp;

1/6 -250 &nb= sp; &nbs= p; ' 1050

SELECT date, (SELECT = SUM(withdrawal) from withdrawal w where w.date=3Dt.date) as withdrawal_amt,

(SELECT SUM(deposit) = FROM deposit d WHERE d.date=3Dt.date) as deposit_amt, 0, 'transaction' as type

FROM trans = t

where year(t.date)=3D2004 and month(t.date)=3D1

UNION

SELECT date, 0, 0, open_amt, = 'opening' as type

FROM mth_balance b

WHERE b.mth=3D 1 and b.year=3D2004 &nbs= p;

I'm able to compute the daily = withdrawal and deposit amount. But who do I get the running end day = total?

It's like waterfall effect, = previous end-day balance =3D next-day opening + (withdrawal + deposit). =

How will the select SQL = like?

Thx in = advance.

--=_NextPart_000_004D_01C3FC06.8ED6C8F0--

All Comments

Leave a comment...

  • 4 Comments
    • In many cases, you can use a self-join or a correlated sub-query to achieve

      the desired cumulative values. If you are looking for a specific query,

      please post your table structures, sample data & expected results. For

      details refer to:

      www.aspfaq.com/5006

      --

      Anith

      #1; Thu, 29 May 2008 04:49:00 GMT
    • Having separate tables for Deposits, Withdrawals and Balances seems rather

      unusual and difficult to maintain. Maybe you would be better off with a

      design like this:

      CREATE TABLE Transactions (trans_date DATETIME PRIMARY KEY, trans_amt

      NUMERIC(10,2) NOY NULL)

      INSERT INTO Transactions VALUES ('20030101',1000)

      INSERT INTO Transactions VALUES ('20040104',-200)

      INSERT INTO Transactions VALUES ('20040105',500)

      INSERT INTO Transactions VALUES ('20040106',-250)

      The following query gives the result you require:

      SELECT trans_date,

      SUM(CASE WHEN trans_amt<0 THEN trans_amt END) AS withdrawal,

      SUM(CASE WHEN trans_amt>0 THEN trans_amt END) AS deposit,

      (SELECT SUM(trans_amt)

      FROM Transactions

      WHERE trans_date <= T.trans_date)

      FROM

      (SELECT trans_date, trans_amt

      FROM Transactions

      UNION ALL

      SELECT '20040101', 0) AS T

      WHERE trans_date >= '20040101'

      AND trans_date < '20040201'

      GROUP BY trans_date

      --

      David Portas

      SQL Server MVP

      --

      #2; Thu, 29 May 2008 04:50:00 GMT
    • Get rid of the separate tables for the various kinds of account

      transactions. You can then write something like this.

      SELECT transaction_code, transaction_date,

      (SELECT SUM(amount)

      FROM BankAccount AS B1

      WHERE B1.transaction_date <= B2.transaction_date) AS balance

      FROM BankAccount AS B2;

      Notice that this query handles both deposits (positive numbers) and

      withdrawals (negative numbers). There is a problem with running totals

      when two items occur at the same time. In this example, the

      transaction code keeps the transactions unique.

      #3; Thu, 29 May 2008 04:51:00 GMT
    • "--CELKO--" <joe.celko.sql.itags.org.northface.edu> wrote in message

      news:a264e7ea.0402251919.60d32528.sql.itags.org.posting.google.com...

      > Get rid of the separate tables for the various kinds of account

      > transactions. You can then write something like this.

      > SELECT transaction_code, transaction_date,

      > (SELECT SUM(amount)

      > FROM BankAccount AS B1

      > WHERE B1.transaction_date <= B2.transaction_date) AS balance

      > FROM BankAccount AS B2;

      > Notice that this query handles both deposits (positive numbers) and

      > withdrawals (negative numbers). There is a problem with running totals

      > when two items occur at the same time. In this example, the

      > transaction code keeps the transactions unique.

      Of all people, why aren't you doing this with sql99?

      With *WHERE B1.transaction_date <= B2.transaction_date*

      you might as well be using a cursor:)

      #4; Thu, 29 May 2008 04:52:00 GMT