Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 calculate tax Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 09, 2014 11:51 AM
 Grasshopper Group: General Forum Members Last Login: Monday, February 17, 2014 3:17 PM Points: 22, Visits: 47
 Hi eveyoneI need to calculate every tax on invoice on every invoice. But some tax on calculate on other tax...ExampleTable 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% nullIt'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 tax1How Can I do my query , with scalar function, table value, stored procedure ??thanks for any hints !
Post #1529498
 Posted Thursday, January 09, 2014 12:07 PM
 SSChampion Group: General Forum Members Last Login: Today @ 2:29 PM Points: 11,969, Visits: 10,994
 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 09, 2014 12:14 PM
 SSChampion Group: General Forum Members Last Login: Today @ 2:11 PM Points: 12,744, Visits: 31,064
 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 ALLSELECT '#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 Group: General Forum Members Last Login: Today @ 3:31 AM Points: 3,590, Visits: 5,097
 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',\$2000UNION ALL SELECT '1001', \$1500;DECLARE @InvoiceTax TABLE( InvoiceNo VARCHAR(10) ,TaxName VARCHAR(10) ,Rate DECIMAL(5,3) ,ParentTax VARCHAR(10));INSERT INTO @InvoiceTaxSELECT '1000','Tax1', 0.05, nullUNION 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 rCTEGROUP 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
 Grasshopper Group: General Forum Members Last Login: Monday, February 17, 2014 3:17 PM Points: 22, Visits: 47
 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-endMy 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-Enthusiastic Group: General Forum Members Last Login: Today @ 7:25 AM Points: 173, Visits: 647
 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 Group: General Forum Members Last Login: Today @ 12:30 PM Points: 572, Visits: 2,070
 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
 Grasshopper Group: General Forum Members Last Login: Monday, February 17, 2014 3:17 PM Points: 22, Visits: 47
 There are a date field, customer# field and even other fields. I want to keep it simple for the exampleFor 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

 Permissions