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 |
|
Gil149
Starting Member
5 Posts |
 Posted - 2014-09-19 : 10:50:44
|
| Good morning,I am new to SQL statements and I am trying to make a query. I have a table (TableA) and in that table I want to query it, returning only values where Field2 - Field3 <> 0. I can do most queries alright, but this is a new scenario I have run into. I appreciate any help. I have attempted to search this but I came up empty handed. |
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-09-19 : 10:56:44
|
| SELECT * FROM TableAWHERE Field2 != Field3 -- the only outcome that would return 0 would require the fields be equal to start |
 |
|
|
Gil149
Starting Member
5 Posts |
Posted - 2014-09-19 : 11:05:41
|
| That's a good idea but I don't think it will work. Field2 is the amount paid on an order and Field3 is the total owed. I wanted the query to only show me orders that are still owed on. So I was thinking Field3-Field2<>0 would work in this case. I apologize I know I started Field2-Field3, I had it backwards there. |
|
|
|
MichaelJSQL
Constraint Violating Yak Guru
252 Posts |
Posted - 2014-09-19 : 11:23:15
|
| If field3 is the amount owed and field 2 is the amount paid, then as soon as field2 = field3 or field 3 = field2, the account is fully paid. If it field2 is greater than field3, the account was paid more than it was owed in which case you owe them money. if field3 is less than field2, the account still owes money. When the fields are equal - the account is settled, so when they are not equal they owe money or you have over collected: all that said- Field3-Field2<>0 is fine as well. |
|
|
|
Gil149
Starting Member
5 Posts |
Posted - 2014-09-19 : 11:26:47
|
| Michael thank you very much, sometimes the simple things allude us. |
|
|
|
jcb267
Constraint Violating Yak Guru
291 Posts |
Posted - 2014-09-19 : 14:20:10
|
| I would try this in two steps.FIRSTselect field3 as 'Owed', field 2 as 'Paid', field3-field2 as 'Balance'into #tableA_tempfrom TableASECONDselect Owed, Paid, balance from #tableA_tempwhere balance <>0If needed you can write the results to a permanent table as long as you have rights so that you can see the balance on a given date and for archiving purposes, etc.... |
|
|
|
|
|
|
|
|
SQL Statement with math