Link to home
Start Free TrialLog in
Avatar of tgfo4927
tgfo4927

asked on

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!
Avatar of santhimurthyd
santhimurthyd
Flag of United States of America image

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
Avatar of tgfo4927
tgfo4927

ASKER

I am getting a syntax error :
Error in SELECT clause: expression near 'CASE'.
Unable to parse query text.
ASKER CERTIFIED SOLUTION
Avatar of santhimurthyd
santhimurthyd
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Very good!