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.
| Author |
Topic |
|
chriskhan2000
Aged Yak Warrior
544 Posts |
 Posted - 2005-06-13 : 10:32:22
|
| I was wondering how it would be possible to do some calculation with same field base on what it outputs. I got some Debits and Credits that I need calculated.Example:ID Amount Type ---- ----- ----- 122 203.00 DR 122 123.00 CR134 122.00 CR134 500.00 DROutput:ID Amount ---- ----- 122 80.00 134 378.00 Was thinking about use CASE but not sure how to apply that. Any ideas? |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-06-13 : 10:48:08
|
| Select id,sum(case when type='DR' then amount else -1*amount end ) from yourTable group by idMadhivananFailing to plan is Planning to fail |
 |
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-06-13 : 11:00:45
|
| Thanks. That works. |
|
|
|
chriskhan2000
Aged Yak Warrior
544 Posts |
Posted - 2005-06-13 : 12:20:11
|
| Is it possible to define more than one boolean in there? For example:Select ID, sum(case when type='DR' or Amount<0 then amount else -1*amount end) from table group by idSome numbers might have negative in there too, so I don't want a negative times a negative which will make it positive. |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-06-13 : 14:07:21
|
| "Some numbers might have negative in there too, so I don't want a negative times a negative which will make it positive."Then you need to clean up your data. CR = creidt = + and DR = Debit = -Either use positives and negatives and drop the TYPE column, or get rid of the bad data.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
|
|
|
Calculation help?