I am very new to SQL. I have created a query to pull out the total of purchase invoices we have created and I have
Union All to the total of purchase credits we have created. The problem I am having is the credits don’t show a minus in front of the amount. When I total the results up, the purchase invoices are adding to the credit instead of taking it away.
Is there a way I can get the credits to show negative and invoices to show positive? Please see my query below.
SELECT T_PURCHASEINVOICE.C_ID, T_PURCHASEINVOICE.C_DATE, T_PURCHASEINVOICE.C_NUMBER, T_PURCHASEINVOICE.C_NETAMOUNT, T_PURCHASEINVOICE.C_GROSSAMOUNT, T_PURCHASEINVOICE.C_TAXAMOUNT, T_TAXRATE.C_CODE FROM T_PURCHASEINVOICE (NOLOCK) JOIN T_PURCHASEINVOICE_TAXLINE (NOLOCK) ON T_PURCHASEINVOICE.C_ID = T_PURCHASEINVOICE_TAXLINE.C__OWNER_ JOIN T_TAXRATE (NOLOCK) ON T_PURCHASEINVOICE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID WHERE T_TAXRATE.C_CODE = '01' AND T_PURCHASEINVOICE.C_TAXAMOUNT <> 0 UNION ALL SELECT T_PURCHASECREDITNOTE.C_ID, T_PURCHASECREDITNOTE.C_DATE, T_PURCHASECREDITNOTE.C_NUMBER, T_PURCHASECREDITNOTE.C_NETAMOUNT, T_PURCHASECREDITNOTE.C_GROSSAMOUNT, T_PURCHASECREDITNOTE.C_TAXAMOUNT, T_TAXRATE.C_CODE FROM T_PURCHASECREDITNOTE (NOLOCK) JOIN T_PURCHASECREDITNOTE_TAXLINE (NOLOCK) ON T_PURCHASECREDITNOTE.C_ID = T_PURCHASECREDITNOTE_TAXLINE.C__OWNER_ JOIN T_TAXRATE (NOLOCK) ON T_PURCHASECREDITNOTE_TAXLINE.C_TAXRATE = T_TAXRATE.C_ID WHERE T_TAXRATE.C_CODE = '01'
This is the result
And this is the result I would like to see
Is this possible?
You just need a case statement in front of the following statement
case when T_PURCHASECREDITNOTE.C_NUMBER = 'PIN' THEN 1 ELSE -1 END * T_PURCHASEINVOICE.C_NETAMOUNT