Hello all,I'm having trouble trying to get the required results with the following SQL code:SELECT bh_bildat, bh_bilnum, bh_biltot, bh_type, bh_payamt, bh_biltot - bh_payamt AS 'Amount O/S'FROM cabilhisWHERE (bh_matter = '00067603')
This is basically from an Invoice table which shows the invoice type (bh_type - This will either be 'b' for bill or 'a' for credit note), invoice amount (bh_biltot) and amount paid (bh_payamt).I have been trying to achieve a new column that if bh_type = 'b' and the bill has been paid in full to return 'Yes' and if not paid in full to return 'No'. If bh_type does not equal 'b', I want to return nothing, so ' '.So to clarify: IF bh_type = 'b' and 'Amount O/S' = '0' THEN return 'Yes'IF bh_type = 'b' and 'Amount O/S' > '0' THEN return 'No'IF bh_type <> 'b' THEN return ' 'I would be grateful for any help on this. Thanks in advance 
Creating a new column based on stated criteria
Posted - 2011-11-08 : 08:14:25
