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.Invoice Number, FS_ARInvoiceHeader.Invoice Date,
FS_ARInvoiceLine.LineItemN umber, FS_ARInvoiceLine.ShipQuant ity, FS_ARInvoiceLine.InvoiceLo calUnitPri ce, FS_Item.ItemDescription,
FS_ItemCost.TotalRolledCos t, FS_ItemCost.CostType, FS_ARInvoiceLine.InvoiceLo calUnitPri ce * FS_ARInvoiceLine.ShipQuant ity AS TotalSales,
FS_ItemCost.TotalRolledCos t * FS_ARInvoiceLine.ShipQuant ity AS TotalCost, FS_ARInvoiceHeader.Invoice Type
FROM FS_ARInvoiceHeader INNER JOIN
FS_Customer ON FS_ARInvoiceHeader.Custome rID = FS_Customer.CustomerID INNER JOIN
FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoi ceHeaderKe y = FS_ARInvoiceLine.ARInvoice HeaderKey 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.ShipQuan tity <> 0) AND (FS_ARInvoiceLine.LineItem Number <> 'FREIGHT') AND (FS_ItemCost.CostType = '0') AND
(FS_ARInvoiceHeader.Invoic eDate = DATEADD(dd, - 1, @yesterday))
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemN umber
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!
SELECT FS_Customer.CustomerID, FS_Customer.CustomerName, FS_ARInvoiceHeader.Invoice
FS_ARInvoiceLine.LineItemN
FS_ItemCost.TotalRolledCos
FS_ItemCost.TotalRolledCos
FROM FS_ARInvoiceHeader INNER JOIN
FS_Customer ON FS_ARInvoiceHeader.Custome
FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoi
FS_Item ON FS_ARInvoiceLine.ItemKey = FS_Item.ItemKey INNER JOIN
FS_ItemCost ON FS_Item.ItemKey = FS_ItemCost.ItemKey
WHERE (FS_ARInvoiceLine.ShipQuan
(FS_ARInvoiceHeader.Invoic
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemN
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!
ASKER
I am getting a syntax error :
Error in SELECT clause: expression near 'CASE'.
Unable to parse query text.
Error in SELECT clause: expression near 'CASE'.
Unable to parse query text.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Very good!
SELECT FS_Customer.CustomerID, FS_Customer.CustomerName, FS_ARInvoiceHeader.Invoice
FS_ARInvoiceLine.LineItemN
FS_Item.ItemDescription,
FS_ItemCost.TotalRolledCos
FS_ARInvoiceLine.InvoiceLo
FS_ItemCost.TotalRolledCos
case when FS_ARInvoiceHeader.Invoice
-(FS_ItemCost.Invoiceamoun
when FS_ARInvoiceHeader.Invoice
+(FS_ItemCost.Invoiceamoun
else
(FS_ItemCost.Invoiceamount
end as InvoiceAmount
FROM FS_ARInvoiceHeader INNER JOIN
FS_Customer ON FS_ARInvoiceHeader.Custome
FS_ARInvoiceLine ON FS_ARInvoiceHeader.ARInvoi
FS_Item ON FS_ARInvoiceLine.ItemKey = FS_Item.ItemKey INNER JOIN
FS_ItemCost ON FS_Item.ItemKey = FS_ItemCost.ItemKey
WHERE (FS_ARInvoiceLine.ShipQuan
(FS_ARInvoiceHeader.Invoic
ORDER BY FS_Customer.CustomerName, FS_ARInvoiceLine.LineItemN