Data is positive number that should be negative

the data pulling from the sql database is positive numbers and based on their invoice type some should be negative.  here's my current sql

SELECT        FS_Customer.CustomerID, FS_Customer.CustomerName, FS_ARInvoiceHeader.InvoiceNumber, FS_ARInvoiceHeader.InvoiceDate,
                         FS_ARInvoiceLine.LineItemNumber, FS_ARInvoiceLine.ShipQuantity, FS_ARInvoiceLine.InvoiceLocalUnitPrice, FS_Item.ItemDescription,
                         FS_ItemCost.TotalRolledCost, FS_ItemCost.CostType, FS_ARInvoiceLine.InvoiceLocalUnitPrice * FS_ARInvoiceLine.ShipQuantity AS TotalSales,
                         FS_ItemCost.TotalRolledCost * FS_ARInvoiceLine.ShipQuantity AS TotalCost, FS_ARInvoiceHeader.InvoiceType
FROM            FS_ARInvoiceHeader INNER JOIN
                         FS_Customer ON FS_ARInvoiceHeader.CustomerID = FS_Customer.CustomerID INNER JOIN
                         FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoiceHeaderKey = FS_ARInvoiceLine.ARInvoiceHeaderKey INNER JOIN
                         FS_Item ON FS_ARInvoiceLine.ItemKey = FS_Item.ItemKey INNER JOIN
                         FS_ItemCost ON FS_Item.ItemKey = FS_ItemCost.ItemKey
WHERE        (FS_ARInvoiceLine.ShipQuantity <> 0) AND (FS_ARInvoiceLine.LineItemNumber <> 'FREIGHT') AND (FS_ItemCost.CostType = '0') AND
                         (FS_ARInvoiceHeader.InvoiceDate = DATEADD(dd, - 1, @yesterday))
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemNumber

I want to be able to tell it that if the InvoiceType is I, then the InvAmount should be positive, else if the Invoice Type is R the InvAmount should be -Amt.  But I can't figure out where to put that in the query or if I should do it on the report in the field expression?

thanks!
tgfo4927Asked:
Who is Participating?
 
santhimurthydCommented:
Sorry I missed the comma operator before Case

Check with now, and also replace the columns name <<Invoiceamount>> with respective to your DB

SELECT        FS_Customer.CustomerID, FS_Customer.CustomerName, FS_ARInvoiceHeader.InvoiceNumber, FS_ARInvoiceHeader.InvoiceDate,
                         FS_ARInvoiceLine.LineItemNumber, FS_ARInvoiceLine.ShipQuantity, FS_ARInvoiceLine.InvoiceLocalUnitPrice,
                                     FS_Item.ItemDescription,
                         FS_ItemCost.TotalRolledCost, FS_ItemCost.CostType,
                                     FS_ARInvoiceLine.InvoiceLocalUnitPrice * FS_ARInvoiceLine.ShipQuantity AS TotalSales,
                         FS_ItemCost.TotalRolledCost * FS_ARInvoiceLine.ShipQuantity AS TotalCost, FS_ARInvoiceHeader.InvoiceType ,             case when FS_ARInvoiceHeader.InvoiceType = 'I' then
                  -(FS_ItemCost.Invoiceamount)
            when FS_ARInvoiceHeader.InvoiceType = 'R' then
                  +(FS_ItemCost.Invoiceamount)
            else
                   (FS_ItemCost.Invoiceamount)
            end as InvoiceAmount
FROM            FS_ARInvoiceHeader INNER JOIN
                         FS_Customer ON FS_ARInvoiceHeader.CustomerID = FS_Customer.CustomerID INNER JOIN
                         FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoiceHeaderKey = FS_ARInvoiceLine.ARInvoiceHeaderKey INNER JOIN
                         FS_Item ON FS_ARInvoiceLine.ItemKey = FS_Item.ItemKey INNER JOIN
                         FS_ItemCost ON FS_Item.ItemKey = FS_ItemCost.ItemKey
WHERE        (FS_ARInvoiceLine.ShipQuantity <> 0) AND (FS_ARInvoiceLine.LineItemNumber <> 'FREIGHT') AND (FS_ItemCost.CostType = '0') AND
                         (FS_ARInvoiceHeader.InvoiceDate = DATEADD(dd, - 1, @yesterday))
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemNumber
0
 
santhimurthydCommented:
check with this querry

SELECT        FS_Customer.CustomerID, FS_Customer.CustomerName, FS_ARInvoiceHeader.InvoiceNumber, FS_ARInvoiceHeader.InvoiceDate,
                         FS_ARInvoiceLine.LineItemNumber, FS_ARInvoiceLine.ShipQuantity, FS_ARInvoiceLine.InvoiceLocalUnitPrice,
                                     FS_Item.ItemDescription,
                         FS_ItemCost.TotalRolledCost, FS_ItemCost.CostType,
                                     FS_ARInvoiceLine.InvoiceLocalUnitPrice * FS_ARInvoiceLine.ShipQuantity AS TotalSales,
                         FS_ItemCost.TotalRolledCost * FS_ARInvoiceLine.ShipQuantity AS TotalCost, FS_ARInvoiceHeader.InvoiceType
             case when FS_ARInvoiceHeader.InvoiceType = 'I' then
                  -(FS_ItemCost.Invoiceamount)
            when FS_ARInvoiceHeader.InvoiceType = 'R' then
                  +(FS_ItemCost.Invoiceamount)
            else
                   (FS_ItemCost.Invoiceamount)
            end as InvoiceAmount

FROM            FS_ARInvoiceHeader INNER JOIN
                         FS_Customer ON FS_ARInvoiceHeader.CustomerID = FS_Customer.CustomerID INNER JOIN
                         FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoiceHeaderKey = FS_ARInvoiceLine.ARInvoiceHeaderKey INNER JOIN
                         FS_Item ON FS_ARInvoiceLine.ItemKey = FS_Item.ItemKey INNER JOIN
                         FS_ItemCost ON FS_Item.ItemKey = FS_ItemCost.ItemKey
WHERE        (FS_ARInvoiceLine.ShipQuantity <> 0) AND (FS_ARInvoiceLine.LineItemNumber <> 'FREIGHT') AND (FS_ItemCost.CostType = '0') AND
                         (FS_ARInvoiceHeader.InvoiceDate = DATEADD(dd, - 1, @yesterday))
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemNumber
0
 
tgfo4927Author Commented:
I am getting a syntax error :
Error in SELECT clause: expression near 'CASE'.
Unable to parse query text.
0
 
tgfo4927Author Commented:
Very good!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.