Change Result in SQL From Positive to Negative

user_16498152 0
Viewed 88

I am using the following code to insert data into a Temp table in SQL:

SELECT bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, trade_qty, (trade_price*trade_qty) AS 'Price_x_Quantity'
INTO #Table1
FROM kst_exchange_trade
WHERE bartt_code IS NOT NULL
GROUP BY bartt_code, bartt_code_description, mele_port_name, expiry_date, trade_price, trade_qty_in_lots, trade_qty

I am running into an issue where trade_qty should be negative when trade_qty_in_lots is negative. How could I build this in to my query to make this change?

enter image description here

sql-server sql
2 Answers

The sign() function will return a value that can be used to match negative/positive.

sign(trade_qty_in_lots) * abs(trade_qty)

If you can guarantee that trade_qty is always positive then naturally the absolute value is unnecessary and can be removed.

user_13222680

If you only want to align the sign, you could always turn the trade_qty field into an expression, such as:

IIF (
    trade_qty_in_lots >= 0, 
            ABS(trade_qty), 
       -1 * ABS(trade_qty)
) AS trade_qty

Note, I've added in an ABS to cover all four cases:

  • Both signs are positive
  • Both signs are negative (not shown in question)
  • trade_qty is positive, trade_qty_in_lots is negative
  • trade_qty is negative, trade_qty_in_lots is positive (not shown in question)
user_6511740
Related