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
 General SQL Server Forums
 New to SQL Server Programming
 SQL Statement with math

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 TableA
WHERE Field2 != Field3 -- the only outcome that would return 0 would require the fields be equal to start

Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

Gil149
Starting Member

5 Posts

Posted - 2014-09-19 : 11:26:47
Michael thank you very much, sometimes the simple things allude us.
Go to Top of Page

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2014-09-19 : 14:20:10
I would try this in two steps.

FIRST
select field3 as 'Owed', field 2 as 'Paid', field3-field2 as 'Balance'
into #tableA_temp
from TableA

SECOND
select Owed, Paid, balance
from #tableA_temp
where balance <>0

If 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....
Go to Top of Page
   

- Advertisement -