Deduct Transaction amount from Master funds. How? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Deduct Transaction amount from Master funds. How?

Author

Topic

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-28 : 00:28:16

I have two tables tblFunds and tblTrans

tblFunds
ID-----FundAmount
----------------------------
01-----10000

tblTrans
----------------------------
trID-----trTransAmt
0000-----0
0001-----50
0002-----100
0003-----200
0004-----150

I want to see this result...

trID-----trTransAmt-----Balance
0000-----0--------------10000
0001-----50-------------9950
0002-----100------------9850
0003-----200------------9650
0004-----150------------9500

Balance result is trTransAmt deducted from FundAmount.

How?

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 00:36:53

select t.trID, t.trTransAmt, f.FundAmount - (select sum(trTransAmt) from tblTrans t2 where t2.trID <= t.trID)
from
tblTrans t
cross join tblFunds f

I'm guessing you will have an account id somewhere though.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-28 : 00:55:53

-- tblFunds
-- ID-----FundAmount
-- ----------------------------
-- 01-----10000
--
--
-- tblTrans
-- ----------------------------
-- trID-----trTransAmt
-- 0000-----0
-- 0001-----50
-- 0002-----100
-- 0003-----200
-- 0004-----150
--
--
-- I want to see this result...
--
-- trID-----trTransAmt-----Balance
-- 0000-----0--------------10000
-- 0001-----50-------------9950
-- 0002-----100------------9850
-- 0003-----200------------9650
-- 0004-----150------------9500

DECLARE @tblFunds TABLE(
ID CHAR(2),
FundAmount MONEY) --Why are you using CHAR for you ID columns?

DECLARE @tbltrans TABLE(
trID CHAR(4),
trTransAmt MONEY)

INSERT @tblFunds(ID, FundAmount)
SELECT '01',10000

INSERT @tblTrans(trID, trTransAmt)
SELECT '0000',0 UNION ALL
SELECT '0001',50 UNION ALL
SELECT '0002',100 UNION ALL
SELECT '0003',200 UNION ALL
SELECT '0004',150

SELECT
tt1.trID,
tt1.trTransAmt,
tf.FundAmount - (
SELECT SUM(trTransAmt)
FROM @tblTrans
WHERE trID <= tt1.trID) AS Balance
FROM
@tblTrans tt1
CROSS JOIN @tblFunds tf

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-28 : 03:01:40

Is there possible way of not using a Select statement? Can we use variable to do this?

Let say...

Select @trTransAmt = (@trTransAmt + trTransAmt), FundAmount - @trTransAmt

something like that?

Want Philippines to become 1st World COuntry? Go for World War 3...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-28 : 17:52:37

That doesn't make sense.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 17:57:33

trID-----trTransAmt-----Balance
0000-----0--------------10000
0001-----50-------------9950
0002-----100------------9850
0003-----200------------9650
0004-----150------------9500

How can you get that in a variable?
You can't set a variable and return a resultset in the same statement.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-28 : 17:58:09

In other words:

That doesn't make sense.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-28 : 18:02:36

Yep - just pointing out why.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-29 : 00:27:13

How about this...

tblFunds
ID-----FundAmount
----------------------------
AA-----10000

tblTrans
----------------------------
trID-----trTransAmt
AAAA-----0
AAAB-----50
AAAC-----100
AAAD-----200
AAAE-----150

I want to see this result...

trID-----trTransAmt-----Balance
AAAA-----0--------------10000
AAAB-----50-------------9950
AAAC-----100------------9850
AAAD-----200------------9650
AAAE-----150------------9500

ID's are alpha formated... How?

Want Philippines to become 1st World COuntry? Go for World War 3...

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 01:28:28

That doesn't make a difference - the query still works.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2004-12-30 : 07:07:38

Just a follow up!

How about without those ID's? How?

Want Philippines to become 1st World COuntry? Go for World War 3...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-12-30 : 12:38:00

We don't see what the problem is with the ID's. Aren't you being a little bit lazy here Jonas? All you have to do is run it like we would:

[code]
-- tblFunds
-- ID-----FundAmount
-- ----------------------------
-- 01-----10000
--
--
-- tblTrans
-- ----------------------------
-- trID-----trTransAmt
-- 0000-----0
-- 0001-----50
-- 0002-----100
-- 0003-----200
-- 0004-----150
--
--
-- I want to see this result...
--
-- trID-----trTransAmt-----Balance
-- 0000-----0--------------10000
-- 0001-----50-------------9950
-- 0002-----100------------9850
-- 0003-----200------------9650
-- 0004-----150------------9500

DECLARE @tblFunds TABLE(
ID CHAR(2),
FundAmount MONEY) --Why are you using CHAR for you ID columns?

DECLARE @tbltrans TABLE(
trID CHAR(4),
trTransAmt MONEY)

INSERT @tblFunds(ID, FundAmount)
SELECT '01',10000

INSERT @tblTrans(trID, trTransAmt)
SELECT 'AAAA',0 UNION ALL
SELECT 'AAAB',50 UNION ALL
SELECT 'AAAC',100 UNION ALL
SELECT 'AAAD',200 UNION ALL
SELECT 'AAAE',150

SELECT
tt1.trID,
tt1.trTransAmt,
tf.FundAmount - (
SELECT SUM(trTransAmt)
FROM @tblTrans
WHERE trID <= tt1.trID) AS Balance
FROM
@tblTrans tt1
CROSS JOIN @tblFunds tf

RESULTS:

AAAA .0000 10000.0000
AAAB 50.0000 9950.0000
AAAC 100.0000 9850.0000
AAAD 200.0000 9650.0000
AAAE 150.0000 9500.0000
[/CODE]

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jonasalbert20
Constraint Violating Yak Guru

300 Posts

Posted - 2005-01-01 : 21:33:17

Nope! derrickleggett...

I just hardly practicing SQL... :D

Want Philippines to become 1st World COuntry? Go for World War 3...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-01-02 : 12:39:10

:) Okay. Was that what you were looking for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources