CODEWITHSUNDEEP
sql-server-2008
James Hinson
James Hinson

Reputation: 23

Converting to negative during select

Trying to figure out how to convert integer and decimal columns to negative within select statements. Any help appreciated.

Begin
  Select 
    OrderInvoiceHeader.Invoice_int As [Invoice #],
    CustomerBillTo.BillToCustomerName As [Customer name],
    OrderInvoiceHeader.Invoice_date As [Invoice date],
    Salesmen.SalesmanName As [Salesman],
    (Select 
      Cast(Round(SUM(BaleInventory.NetWeight_dec), 0) As int)
      from dbo.BaleInventory

      Where dbo.BaleInventory.BaleNumber In     
       (Select OrderInvoiceBales.BaleNumber 
       from OrderInvoiceBales 
       Where OrderInvoiceBales.Invoice_int = OrderInvoiceHeader.Invoice_int)) 
       as [Lbs invoiced],

Here I need to convert InvoiceAdjustment_dec to negative if OrderInvoiceHeader.InvoiceType = "3", same is true for other dollar values

OrderInvoiceHeader.InvoiceAdjustment_dec As [Adjustment $],
OrderInvoiceHeader.InvoiceFreight_dec As [Freight $],
OrderInvoiceHeader.InvoiceMerchandise_dec As [Merch $],
OrderInvoiceHeader.InvoiceTotal_dec As [Total $],
OrderInvoiceHeader.FreightPaid_dec As [Frt paid $]


From OrderInvoiceHeader

  Left Outer Join dbo.CustomerBillTo
  On OrderInvoiceHeader.BillToCustomer_int = CustomerBillTo.BillToCustomer_int

  Left Outer Join dbo.Salesmen
  On OrderInvoiceHeader.Salesman_int = Salesmen.Salesman_int

  Where 
    OrderInvoiceHeader.Invoice_date >= @From_date and
    OrderInvoiceHeader.Invoice_date <= @To_date

    Order By OrderInvoiceHeader.Invoice_int
End

Upvotes: 0

Views: 60

Answers (1)

Lamak
Lamak

Reputation: 70638

Use a CASE expression:

SELECT ..... --all your other columns here
       CASE WHEN OrderInvoiceHeader.InvoiceType = '3' 
       THEN -1*InvoiceAdjustment_dec 
       ELSE InvoiceAdjustment_dec END AS InvoiceAdjustment_dec

Upvotes: 2

Related Questions