Calculating Transaction Amounts

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 2005 Forums

Transact-SQL (2005)

Calculating Transaction Amounts

Author

Topic

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-21 : 12:18:08

I have a table called tblTransactions with following fieldnames and their data types:

TransactionDate dateTime
TransactionAmount Money
TransactionMethod nvarchar(255)
TransactionType nvarchar(255)
TransactionPurpose nvarchar(255)
Dept nvarchar(255)
Comments nvarchar(255)

TransactionType could be PAYMENT or CHARGE or ADJUSTMENT

The users of our app would prefer a transaction display exactly like the one below:


TransactioDate    Charge   Payment         Adjustment         Balance
11/17/2009                 $100                               $550
11/11/2009                                  ($50)             $650
11/04/2009                  $50                               $700
11/01/2009         $750                                       $750

They want the new balance amount to show under the Balance column, rather than
the amount that is added or subtracted.

Query is sorted by TransactionDate in descending order

Can you please, please help with the query?

Many thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 01:08:47

[code]SELECT TransactionDate,Dept,
SUM(CASE WHEN TransactionType='CHARGE' THEN TransactionAmount ELSE 0 END) AS Charge,
SUM(CASE WHEN TransactionType='PAYMENT' THEN TransactionAmount ELSE 0 END) AS Payment,
SUM(CASE WHEN TransactionType='ADJUSTMENT' THEN TransactionAmount ELSE 0 END) AS Adjustment,
t1.Balance + SUM(CASE WHEN TransactionType IN ('PAYMENT','ADJUSTMENT') THEN TransactionAmount ELSE -1 * TransactionAmount END) AS Balance
FROM tblTransactions t
CROSS APPLY(SELECT TOP 1 Balance
FROM tblTransactions
WHERE Dept=t.Dept
AND TransactionDate<t.TransactionDate
ORDER BY TransactionDate DESC)t1
GROUP BY TransactionDate,Dept
ORDER BY TransactionDate,dept
[/code]

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-22 : 01:50:23

Thank you so much for your response visakh16.

I am getting following error:

Invalid column name 'Balance'.

What am I doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 11:49:10

do you have column Balance in your table?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-23 : 08:21:12

No, I don't have column balance. That is to be generated on the fly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:21:58

then whats the rule for generating that?

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-23 : 10:44:11

I suppose you create a variable and we call it balance,

Then if it is charge, you add the charge amount to the balance (balance=balance+amount)
If payment, you deduct from the balance (balance=balance-amount)
If adjustment, you add to balance (balance=balance+amount) unless the intent is to confuse.

So, I think I do the general concept. I just need help with putting these pieces, more importantly, the layout together.

if you can assist, it will be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 11:05:02

[code]
SELECT TransactionDate,Dept,
SUM(CASE WHEN TransactionType='CHARGE' THEN TransactionAmount ELSE 0 END) AS Charge,
SUM(CASE WHEN TransactionType='PAYMENT' THEN TransactionAmount ELSE 0 END) AS Payment,
SUM(CASE WHEN TransactionType='ADJUSTMENT' THEN TransactionAmount ELSE 0 END) AS Adjustment,
t1.Balance + SUM(CASE WHEN TransactionType IN ('PAYMENT','ADJUSTMENT') THEN TransactionAmount ELSE -1 * TransactionAmount END) AS Balance
FROM tblTransactions t
CROSS APPLY(SELECT SUM(CASE WHEN TransactionType IN ('PAYMENT','ADJUSTMENT') THEN TransactionAmount ELSE -1 * TransactionAmount END) AS Balance
FROM tblTransactions
WHERE Dept=t.Dept
AND TransactionDate<t.TransactionDate)t1
GROUP BY TransactionDate,Dept
ORDER BY TransactionDate,dept

[/code]

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-23 : 12:15:58

You are true Yak Herder; thank you very much.

Problem is that I am getting mostly nulls in Balance column, plus too many duplications.

balance is only supposed to be null if no transactions have ocurred.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 12:31:00

[code]SELECT TransactionDate,Dept,
SUM(CASE WHEN TransactionType='CHARGE' THEN TransactionAmount ELSE 0 END) AS Charge,
SUM(CASE WHEN TransactionType='PAYMENT' THEN TransactionAmount ELSE 0 END) AS Payment,
SUM(CASE WHEN TransactionType='ADJUSTMENT' THEN TransactionAmount ELSE 0 END) AS Adjustment,
ISNULL(t1.Balance,0) + ISNULL(SUM(CASE WHEN TransactionType IN ('PAYMENT','ADJUSTMENT') THEN TransactionAmount ELSE -1 * TransactionAmount END),0) AS Balance
FROM tblTransactions t
CROSS APPLY(SELECT SUM(CASE WHEN TransactionType IN ('PAYMENT','ADJUSTMENT') THEN TransactionAmount ELSE -1 * TransactionAmount END) AS Balance
FROM tblTransactions
WHERE Dept=t.Dept
AND TransactionDate<t.TransactionDate)t1
GROUP BY TransactionDate,Dept
ORDER BY TransactionDate,dept
[/code]

simflex
Constraint Violating Yak Guru

327 Posts

Posted - 2009-11-23 : 14:10:47

Thank you very, very much.

I will try and figure out why we are still getting the duplicate records.

Those dup records don't exist on the db.

I am grateful for your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources