calculate tax

Question

calculate tax

dquirion78

Ten Centuries

Points: 1240
More actions
January 9, 2014 at 11:51 am

#281932

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 !
May 3, 2023 at 6:39 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 2

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 Camps SSC Guru Points: 86968 More actions · Answer 3

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![/I]
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

dquirion78 Ten Centuries Points: 1240 More actions · Answer 4

wow dwain.c thanks exactly what I wanted sql server is so strong and both of you 🙂

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 🙂

and sorry for my english...

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 5

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 SSCertifiable Points: 6230 More actions · Answer 6

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 Ten Centuries Points: 1240 More actions · Answer 7

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...