SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


calculate tax


calculate tax

Author
Message
dquirion78
dquirion78
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 74
Hi eveyone

I need to calculate every tax on invoice on every invoice. But some tax on calculate on other tax...

Example

Table Invoice
#Invoice Total
#1000 2000$
#1001 1500$

Table InvoiceTax
#Invoice TaxName Rate Calculate tax on
#1000 Tax1 5% null
#1000 Tax2 9% Tax1
#1001 Tax3 7% null


It's simple for invoice 1001 but for invoice 1000 is more complicated because Tax2 is calculated on total+tax1= so (2000*1(0.05) + 2000)=2100 *0.09 =189$ for tax2 and 100$ for tax1

How Can I do my query , with scalar function, table value, stored procedure ??

thanks for any hints !
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63999 Visits: 17974
Hi and welcome to the forums!!! It is considered best practice around here to post ddl and sample data in a consumable format. You can read about how to post that by following the link in my signature for best practices.

The biggest challenge I see here is that there does not appear to any way to sort your tax table accurately. The only value you have listed are strings (Tax1, Tax2 etc). This is fine to have a name for the taxes but you need to have someway to sort them (and sorting by strings is not the best choice). What happens if you have a third nested tax calculation? You have to know which order to process these calculations to get it correct.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Lowell
Lowell
SSC Guru
SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)SSC Guru (73K reputation)

Group: General Forum Members
Points: 73805 Visits: 40974
if this is for a class or homework, we can point you in the right direction;
from experience, your structure is wrong;
individual products or items on the invoice need to have a flag which identifies whether they are taxable or not, and maybe some kind of tax class (europes VAT, right?)

in the US, for example, foodstuffs are generally non taxable, but prepared food, appliances, etc can have local sales tax; cars might have an additional tax on them based on the state;

so taxes are generally much more complex than grabbing totals off of an invoice.

anyway, the basic solution is to calculate them in line with your data; [invoicetotal] * tax calculation.


/*
#Invoice InvoiceTotal isTaxable tax1 tax2
#1000 2000.00 1 100.0000 189.000000
#1001 1500.00 1 75.0000 141.750000
*/
;WITH MyCTE([#Invoice],[InvoiceTotal], [isTaxable])
AS
(
SELECT '#1000',2000.00,1 UNION ALL
SELECT '#1001',1500.00,1
)
SELECT
[#Invoice],
[InvoiceTotal],
[isTaxable],
[tax1]= [InvoiceTotal] * 0.05,
[tax2] = ([InvoiceTotal]*1.05) *0.09
FROM MyCTE;



Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
dwain.c
dwain.c
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18221 Visits: 6431
While Sean and Lowell are essentially correct in their suggestions that there's probably a better way to structure your tables to handle the tax consequences of an invoice, I have given up trying to predict the weirdness of taxation across the many perturbations apparent across 188 countries in the world.

Still, that double taxation thing just seems wrong to me.

dquirion78 (1/9/2014)


How Can I do my query , with scalar function, table value, stored procedure ??



Actually the answer is none of the above. What you are describing is a hierarchical taxation structure and hierarchies are generally resolved using a recursive CTE.

First, some DDL and consumable sample data is needed. The next time you post, help us help you by doing this part yourself.


DECLARE @Invoice TABLE
(
InvoiceNo VARCHAR(10)
,Total MONEY
);
INSERT INTO @Invoice
SELECT '1000',$2000
UNION ALL SELECT '1001', $1500;

DECLARE @InvoiceTax TABLE
(
InvoiceNo VARCHAR(10)
,TaxName VARCHAR(10)
,Rate DECIMAL(5,3)
,ParentTax VARCHAR(10)
);
INSERT INTO @InvoiceTax
SELECT '1000','Tax1', 0.05, null
UNION ALL SELECT '1000','Tax2', .09, 'Tax1'
UNION ALL SELECT '1001','Tax3', .07, null;



Then we can calculate tax per invoice as follows.


WITH rCTE AS
(
SELECT a.InvoiceNo, Total, TaxAmt=CAST(Total*Rate AS MONEY), TaxName
FROM @Invoice a
JOIN @InvoiceTax b ON a.InvoiceNo = b.InvoiceNo
WHERE b.ParentTax IS NULL
UNION ALL
SELECT a.InvoiceNo, Total, TaxAmt=CAST((Total + TaxAmt)*Rate AS MONEY), b.TaxName
FROM rCTE a
JOIN @InvoiceTax b ON a.TaxName = b.ParentTax
)
SELECT InvoiceNo, Total=MAX(Total), TaxAmt=SUM(TaxAmt)
FROM rCTE
GROUP BY InvoiceNo;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
dquirion78
dquirion78
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 74
wow dwain.c thanks exactly what I wanted sql server is so strong and both of you Smile

I'm learning sql server and I'm trying to convert access back-end to sql back-end

My business is fairly simple all products are taxable.
But you are probably right, my structure is not the best, probably with an order on tax instead of hierarchies. l just don't know the exatcly logicial on tax on Canada.

I have federal tax and provincial tax. provincial tax is calculated on (total +federal tax) but what's happen if a 3rd tax come out ???

thanks all ! and next time l'll do a better structure !! because l'll probably need again later ... and it's so nice to have so good advice Smile

and sorry for my english...
crmitchell
crmitchell
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1270 Visits: 1794
As an additional complication.

I'd suggest you also store the invoice date.
Tax rates can change and in general the tax payable will be charged at the rate applicable at the time the invoice is issued.

In practice when a rate changes the local tax office may issue guidelines as to how the changes should be applied for jobs spanning the rate change.
Steven.Howes
Steven.Howes
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1654 Visits: 2354
I'm pretty sure(though not a tax expert by any ANY means) that federal and provincial taxes are both applied to the same amount.

So more like

(GST * amount) + (PST * amount)

The only province I'm not sure about is Quebec. They always seem to do things differently (Not a bad thing most of the time).
dquirion78
dquirion78
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 74
There are a date field, customer# field and even other fields. I want to keep it simple for the example

For quebec it's

(GST * amount) + (PST * ((GST * amount)+ amount))

100 * 0.05 = (0.09 * ((0.05*100=5)+100) = 5 +9.45= 14.45$ tax total and not 5$+9$...

in quebec =federal tax are taxable... what a joke...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search