showing tax in a seperate row

Question

Post reply

showing tax in a seperate row

S-322532

SSCommitted

Points: 1613
More actions
May 25, 2010 at 10:17 pm

#221599

Hi,
I'm having a table like below
SNO Description tax amount
1 car purchase 3% 5000
the amount column has the actual price + the tax for the price (4350 + 150)
Now i want to project data like below
SNO Description tax amount
1 car purchase 3% 4350
2 AAA 0% 150
any suggestions and tips on this will be of great help 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

try this

declare @t table (amt int, p int)

insert into @t select 40000, 10 union select 4000, 30

select amt , p , ((amt*p)/100 + amt) as total from @t

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

S-322532 SSCommitted Points: 1613 More actions · Answer 2

Bhuvnesh, thanks for the response. 🙂

the snippet you provided shows the amount sans tax in a separate row, but i need the tax to be shown in a separate row.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

What you are asking really should be done in the UI not SQL. Here is another possible solution based on your original post.

declare @TestTab table (

sno int,

PurchaseDescription varchar(32),

TaxPercentage tinyint,

PurchaseAmount money);

insert into @TestTab select 1, 'Car Purchase', 3, 5000.00;

select * from @TestTab;

select

sno,

1,

PurchaseDescription,

TaxPercentage,

cast(round(PurchaseAmount / (1 + (TaxPercentage / 100.00)), 2) as money) as PurchaseAmt

from

@TestTab

union

select

sno,

2,

PurchaseDescription,

TaxPercentage,

PurchaseAmount - cast(round(PurchaseAmount / (1 + (TaxPercentage / 100.00)), 2) as money)

from

@TestTab

;

S-322532 SSCommitted Points: 1613 More actions · Answer 4

Hi Lynn Pettis,

Thanks for the response. 🙂

I'll try it.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

S-322532 (5/26/2010)
i need the tax to be shown in a separate row.

A bit starnge requirement , anyways for that you need to put some lookup table which will store different IDs for amount (let says 1 ) and tax( let says 2 ) now the query given by lynn contains 2nd columns for the same.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)