CODEWITHSUNDEEP
sqlsql-server
Davis
Davis

Reputation: 47

Change Result in SQL From Positive to Negative

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

Upvotes: 1

Views: 140

Answers (2)

shawnt00
shawnt00

Reputation: 17953

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.

Upvotes: 4

David542
David542

Reputation: 110592

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)

Upvotes: 2

Related Questions