Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

calculate tax Expand / Collapse
Author
Message
Posted Thursday, January 9, 2014 11:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:20 AM
Points: 36, 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 !


Post #1529498
Posted Thursday, January 9, 2014 12:07 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:59 PM
Points: 13,007, Visits: 12,426
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 Moden's 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)
Post #1529504
Posted Thursday, January 9, 2014 12:14 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:34 PM
Points: 12,903, Visits: 32,144
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1529511
Posted Sunday, January 12, 2014 7:12 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 7:26 PM
Points: 3,420, Visits: 5,350
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!
Post #1530173
Posted Sunday, January 12, 2014 10:24 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:20 AM
Points: 36, Visits: 74
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...
Post #1530175
Posted Monday, January 13, 2014 2:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 216, Visits: 859
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.
Post #1530210
Posted Monday, January 13, 2014 7:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 8:05 AM
Points: 589, Visits: 2,105
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).


Post #1530278
Posted Monday, January 13, 2014 7:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 6:20 AM
Points: 36, 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...

Post #1530289
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse