January 10, 2013 at 11:40 pm
Hai friends
create table company_order
(
company varchar(100),
location varchar(100),
order_no varchar(100),
order_qty varchar(100),
rate varchar(100),
total_value varchar(100)
)
insert into company_order
values('a','mumbai','po-256',500,200,100000)
insert into company_order
values(''a','chennai','po-50',50,25,1250)
create table taxes
(
order_no varchar(100),
company varchar(100),
location varchar(100),
tax_code varchar(100),
)
insert into taxes
values
('po-256','a','Mumbai','Exciseduty10%')
insert into taxes
values
('po-256','a','Mumbai','CESS2%')
insert into taxes
values
('po-256','a','Mumbai','HECESS10%')
insert into taxes
values
('po-256','a','Mumbai','VAT4%')
friends
now i wanna calculate the tax value depends on total value
seperately
Exciseduty formula (percentage)=totalvalue*10/100
cess=Exciseduty*2/100
HECESS=cess*10/100
vat=(total-value+excise duty+cess+hecss)*vat percentage/100
total-tax =(Exciseduty+cess=HECESS+VAT)
(Note tax_code ED12%,cess 5% like it may variate for each po_no)
here EDpecrntage may variate,depnds the variance ve to calculate values of taxez
January 11, 2013 at 1:17 am
raghuldrag (1/10/2013)
(Note tax_code ED12%,cess 5% like it may variate for each po_no)here EDpecrntage may variate,depnds the variance ve to calculate values of taxez
Couple of question
1) what is the relevance of tax_code column here, does it play any role in calculation you mentioned above?
2) where you want this data ? to store in table or to display in application (or report) ?
3) can you add another table in current desiagn like TAX_DETAILS ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2013 at 1:33 am
dis for reaport purpose......
January 11, 2013 at 1:43 am
There are a number of concerns in your design, though I dont know how much scope you have to change the design, it would be worth considering the following,
1) Total_Value column on the company order table really should be a money or decimal datatype.
2) splitting the taxes table into two (see below) to give more scope for change.
I would consider doing something like this
create table #company_order
(
company varchar(100),
location varchar(100),
order_no varchar(100), --FK
order_qty varchar(100),
rate varchar(100),
total_value money
)
insert into #company_order
values('a','mumbai','po-256',500,200,100000)
,('a','chennai','po-50',50,25,1250)
create table #company_order_taxes
(
order_no varchar(100),
company varchar(100),
location varchar(100),
tax_rate_id int --FK
)
insert into #company_order_taxes
values
('po-256','a','Mumbai',1)
,('po-256','a','Mumbai',2)
,('po-256','a','Mumbai',3)
,('po-256','a','Mumbai',4)
create table #tax_rate (
tax_rate_id Int, --pk
tax_rate_description varchar(100),
tax_rate decimal (9,2)
)
insert into #tax_rate
values
(1,'Exciseduty10%',10)
,(2,'CESS2%',2)
,(3,'HECESS10%',10)
,(4,'VAT4%',4)
;With Get_Order_Tax
AS
(
Select
ct.order_no
,SUM(Case
When tax_rate_description like 'Exciseduty%' then (tax_rate/100)
Else NULL
End) as Excise_duty_rate
,SUM(Case
When tax_rate_description like 'Cess%' then (tax_rate/100)
Else NULL
End) as CESS_rate
,SUM(Case
When tax_rate_description like 'HECESS%' then (tax_rate/100)
Else NULL
End) as HECESS_rate
,SUM(Case
When tax_rate_description like 'VAT%' then (tax_rate/100)
Else NULL
End) as VAT_rate
From #company_order_taxes ct
JOIN #tax_rate tr on ct.tax_rate_id=tr.tax_rate_id
Group by
ct.order_no
)
Select
*
,gross_order_value-Pre_Vat_Tax_Payable-Vat_Payable Nett_Order_Value
From
(
Select
Company
,co.order_no
,co.total_value gross_order_value
,total_value*Excise_duty_rate excess_duty_due
,total_value*CESS_rate CESS_Tax_Due
,total_value*HECESS_rate HECESS_Tax_Due
,total_value*(Excise_duty_rate+CESS_rate+HECESS_rate) Pre_Vat_Tax_Payable
,(total_value-(total_value*(Excise_duty_rate+CESS_rate+HECESS_rate)))*Vat_Rate Vat_Payable
from #company_order co
JOIN Get_Order_Tax ot on co.order_no=ot.order_no
) taxes
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 11, 2013 at 1:44 am
raghuldrag (1/11/2013)
dis for reaport purpose......
can you tell me what columns you need in report other then these tax(calculated) columns ?
and do you have any related query ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 11, 2013 at 3:01 am
In your description, you've not described any use for the table "taxes".
This fulfils your description: -
SELECT *
FROM (SELECT co.company, co.location, co.order_no,
CAST(co.order_qty AS INT) AS order_qty,
CAST(co.rate AS MONEY) AS rate,
CAST(co.total_value AS MONEY) AS total_value
FROM company_order co
) a
CROSS APPLY (SELECT (total_value*10.0)/100.0) b(Exciseduty)
CROSS APPLY (SELECT (Exciseduty*2.0)/100.0) c(cess)
CROSS APPLY (SELECT (cess*10.0)/100.0) d(HECESS)
CROSS APPLY (SELECT ((total_value+Exciseduty+cess+HECESS)*20.0)/100.0) e(vat)
CROSS APPLY (SELECT Exciseduty+cess+HECESS+vat) f(total_tax);
Returns: -
company location order_no order_qty rate total_value Exciseduty cess HECESS vat total_tax
------- -------- -------- ----------- --------------------- --------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
a mumbai po-256 500 200.00 100000.00 10000.0000000000 200.0000000000000000 20.000000000000 22044.00000000 32264.00000000
a chenna po-50 50 25.00 1250.00 125.0000000000 2.5000000000000000 0.250000000000 275.55000000 403.30000000
Now, I can be reasonably certain when I guess that the above is not what you actually want. So, could you provide your expected results, based on the sample data that you've provided please?
Note: I agree with Jason where he comments on changing your schema if possible. I haven't read the changes he has suggested though, so make sure you think it through if you go down that route.
January 11, 2013 at 3:24 am
Cadavre,
The changes I suggested were to create a seperate table for tax rates and reference it from current taxes table with a possible name change to company_order_taxes and reference the new table rather than having hard coded values.
The other change was to turn the Total_value column from a varchar(100) to a money or decimal datatype.
As you say though consider the changes carefully, as they may not be an optimal design and would require a refactor of existing code at database and application level.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply