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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tgfo4927Author Commented:
Very good!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.