Calculating total using 2 fields

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 total using 2 fields

Author

Topic

maxjam
Starting Member

9 Posts

Posted - 2007-01-03 : 11:18:09

Hi all,

I have a table in which there are 2 related fields

field1 = value
field2 = creditdebit

I need to calculate the sum of the value field for all records but i also need to check the creditdebit field to find out whether the value field is a positive or negative ammount, BEFORE i add it to the SUM running total.

eg

if creditdebit = "CR" THEN value = value * -1
else if creditdebit = "DR" THEN value stays the same.

I havent a clue how to do this, any help is appreciated.

Max

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-03 : 11:27:58

Use a CASE expression.

select case creditdebit when "CR" then -1 else 1 end * value as CalculatedValue
from yourtable

- Jeff

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-04 : 08:11:21

or

If you use Reports Like Crystal Reports and you want to show Running Total there

Create a Formula field having this code


CurrencyVar C;
If {creditdebit}="CR" then
    C:=-1*value 
else
   C:=value

And use this as source field for your Running Total

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources