Add negative to credited invoices only-CodePudding

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?

CodePudding user response：

You just need a case statement in front of the following statement

T_PURCHASEINVOICE.C_NETAMOUNT,

with

case when T_PURCHASECREDITNOTE.C_NUMBER  = 'PIN' THEN 1 ELSE -1 END * T_PURCHASEINVOICE.C_NETAMOUNT