Jeff Moden (11/3/2012)
malcolm.garbett (11/3/2012)
Thanks guys, but...Let me try and be more clear.
The data is coming from a table where the monetary value is always "just" a value so the output looks like:
Example:
Columns.
Inv/Credit Amount
SI-100 10.00
SI-200 12.75
SI- 300 250.00
SC-100 18.50
Where in the Inv/Credit column the id is prefixed with a "SC" I know that's a credit note so in this report - written in BIRT - for the accounts guys, I need to leave the "SI" values "as is" but have a minus symbol where there is a "SC" so it outputs like this:
Columns.
Inv/Credit Amount
SI-100 10.00
SI-200 12.75
SI- 300 250.00
SC-100 -18.50
You may have to change the column names and, certainly, the table name, but the following will do it quite nicely.
SELECT [Inv/Credit],Amount = CASE WHEN LIKE 'SC%' THEN Amount ELSE -Amount END
FROM dbo.YourTable
;
That's close, but I fear I have misled you...
The SI/SC is not the value field, that's the identifier, and another field is the value.
I tried this, based on your help:
SELECT
invoiceitems.nominalaccountid,
traders.name,
invoiceitems.partid,
invoices.id,
invoiceitems.itemnumber,
invoices.taxdate,
invoiceitems.homenettvalue, CASE WHEN invoices.id LIKE 'SI%' THEN invoiceitems.homenettvalue ELSE -invoiceitems.homenettvalue END,
invoiceitems.hometaxvalue
from dbo.table
This adds a column - with no name - repeats the values from the invoiceitems.homenettvalue BUT adds the minus symbol when the invoices.id = 'SC% 'which is what I'm after, but not in a new column. So at the moment it now looks like this:
nominalaccountidnamepartididitemnumberhomenettvalue(No column name)hometaxvalue
1129-BALSHEETElektrameric Ltd294-34200SI-02021710.000.000.00
1129-BALSHEETElektrameric Ltd294-35610SI-02021720.000.000.00
1129-BALSHEETBryan Donkin RMG Gas Cont294-23090SI-020350714.0014.002.45
1160-BALSHEETShenzhen Oska Measurement SystemsE8UPGRADEMISCSC-03058511619.8199-1619.81990.00
1160-BALSHEETShenzhen Oska Measurement SystemsE8UPGRADEMISCSC-03059311619.8199-1619.81990.00
1160-BALSHEETShenzhen Oska Measurement SystemsE8UPGRADEMISCSI-03059111619.81991619.81990.00
2210-BALSHEETISIS Precision Engineering Ltd.SR000000SI-02989920.000.000.00
2210-BALSHEETISIS Precision Engineering Ltd.SR000000SI-02989930.000.000.00
2210-BALSHEETSpire Machining Ltd.MS0003INSI-0302471295.00295.0051.63