Hey Zar,
Actually some guy responded to you, and came up with some case statement in t-sql.
But I'm more curious about this table structure you have.
Let me know what the surrounding objects/table/situation is, but here's my thoughts just on what I saw in your post:
Summing items horizontally is difficult to do normally (since you need to check if this column equals this and that column equals that, etc.) Assuming there is some invoice being taxed, let me give you another option of design:
object:Invoice
--------------
InvoiceID int
(insert other
invoice stuff
here)
object: Tax
--------------
TaxID
TaxName
TaxPercent
Association object: Invoice_Tax
---------------
TokenRowID
InvoiceID
TaxID
TaxAmount
ok, follow me here:
Lets say the invoice can have multiple taxes applied to it (and GST can come in twice...btw, i didn't know you were Canadian!) GST would be in the taxes table as
TaxID = 1
TaxName = 'GST'
TaxPercent = .07
Now lets say there are two instances of gst for Invoice 1.
In the association table (keep in mind that this scenario assumes the methodology of mapping classes to tables), there would be these two records:
RowID = 1
InvoiceID = 1
TaxID = 1
Amount = 50.00
RowID = 2
InvoiceID = 1
TaxID = 1
Amount = 50.00
So what does this get us? Well, now that we have it all normalized, running a query to find the total of any given tax is simple:
Select Sum(Amount) From Invoice_Tax Where InvoiceID = 1
And TaxID = 1
Let me know what you think, and if I'm way off on how your app is supposed to work.
And where in Canada are you?!
Jack