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 |
|
getcarter15
Starting Member
4 Posts |
 Posted - 2012-05-08 : 05:56:11
|
| HiI have a table containing invoices and credit notes and i need to sum them for a total by adding all the invoice values while subtracting the credit note values. However, both have positive values in their 'amount' field and the two are distinguished by another column named 'DocumentType'. I think there are probably several ways to do this but I haven't found a way that isn't quite messy when it seems like there would be an fairly simple query to do it. I'm fairly new to SQL and the best I've come up with is to use CASE statements in the SUM to get two columns in the results set - one for the invoice total and one for the creditnote total but ideally i'd like this as one total. Can anyone help me on this?thanksCarter |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-05-08 : 06:16:09
|
[code]SUM(case when DocumentType = 'Invoice' then amount else -amount end)[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
getcarter15
Starting Member
4 Posts |
Posted - 2012-05-08 : 09:32:19
|
| Thanks, that's exactly what I needed. :-) |
|
|
|
|
|
|
sum and subtract from same column