CODEWITHSUNDEEP
sqlsql-server
user10054130
user10054130

Reputation:

multiplying column

I have an sql script below.

SELECT
     InvoiceNo
    ,InvoiceType
    ,Amount
    ,OrderAmount
    ,ShippingAmount
    ,TruckTaxAmount
    ,PreShippingAmount
FROM truckdb AS t1
INNER JOIN truckdetails AS t2 ON tl.truckdetail = t2.truckid
WHERE [shipping date] > = '01-01-2011'

And sample data

+--------+-------------+---------+-------------+----------------+------------+----------+
| InvNo  | InvoiceType | Amount  | OrderAmount | ShippingAmount | TruckTxAmt | PreShAmt |
+--------+-------------+---------+-------------+----------------+------------+----------+
|    001 | ckt         |    1200 |         544 |            666 |         23 |       11 |
|    002 | tkp         |    1300 |         544 |            133 |         11 |       11 |
|    009 | ckt         |    1222 |         221 |            122 |        221 |      566 |
+--------+-------------+---------+-------------+----------------+------------+----------+

I have several invoice types. I want to show one particular CKT InvoiceType - Amount, OrderAmount, ShippingAmount, TruckTaxAmount in negative. I tried to multiply using when statement after where clause. But something is wrong.

Upvotes: 1

Views: 53

Answers (2)

user10054130
user10054130

Reputation:

Thanks, @Tab Alleman

SELECT ... CASE WHEN InvoiceType='CKT' THEN Amount * -1.00 ELSE    Amount END AS Amount, 
      CASE WHEN InvoiceType='CKT' THEN OrderAmount * -1.00 ELSE OrderAmount END AS 
          OrderAmount, (etc) ... FROM ...    ------------------------------------------------------------------------

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31775

You need to use the CASE WHEN in the SELECT clause, not after the WHERE clause:

SELECT 
...
CASE WHEN InvoiceType='CKT' THEN Amount * -1.00 ELSE Amount END AS Amount,
CASE WHEN InvoiceType='CKT' THEN OrderAmount * -1.00 ELSE OrderAmount END AS OrderAmount,
(etc)
...
FROM ...

Upvotes: 2

Related Questions