## calculate tax

 Author Message dquirion78 SSChasing Mays Group: General Forum Members Points: 620 Visits: 74 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 ! Sean Lange SSC Guru Group: General Forum Members Points: 149611 Visits: 18575 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 SSC Guru Group: General Forum Members Points: 184451 Visits: 41569 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--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 SSC-Forever Group: General Forum Members Points: 44489 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',\$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!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dquirion78 SSChasing Mays Group: General Forum Members Points: 620 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-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... crmitchell SSCrazy Group: General Forum Members Points: 2389 Visits: 1963 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 Hall of Fame Group: General Forum Members Points: 3684 Visits: 2368 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 SSChasing Mays Group: General Forum Members Points: 620 Visits: 74 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...