New Zenner
- Join Date:
- Dec 2007
- Posts:
- 64
- Plugin Contributions:
- 0
Fl. County Sales tax sql quandry
I am trying to use a portion of a database I had on an old website to handle sales tax calculation. In Florida, the base tax is 6%, but counties have the ability to add a tax surcharge (even down to a zip code level).
I dropped the two tables below and replaced them with the full list of counties (and zip codes) and the respective tax surcharges.
I have the tax rate set at 6%. When I test a purchase, it calculates at the 6% rate.
Is there something I am missing?
--
-- Table structure for table tax_rates
CREATE TABLE IF NOT EXISTS tax_rates (
tax_rates_id int(11) NOT NULL auto_increment,
tax_zone_id int(11) NOT NULL default '0',
tax_class_id int(11) NOT NULL default '0',
tax_priority int(5) default '1',
tax_rate decimal(7,4) NOT NULL default '0.0000',
tax_description varchar(255) collate latin1_german2_ci NOT NULL default '',
last_modified datetime default NULL,
date_added datetime NOT NULL default '0001-01-01 00:00:00',
PRIMARY KEY (tax_rates_id),
KEY idx_tax_zone_id_zen (tax_zone_id),
KEY idx_tax_class_id_zen (tax_class_id)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci AUTO_INCREMENT=3 ;
--
-- Dumping data for table tax_rates
INSERT INTO tax_rates (tax_rates_id, tax_zone_id, tax_class_id, tax_priority, tax_rate, tax_description, last_modified, date_added) VALUES (1, 1, 1, 1, 6.0000, 'FL SalesTax', '2008-03-01 11:02:55', '2008-01-15 00:08:09');
--
-- Table structure for table tax_rates_local
CREATE TABLE IF NOT EXISTS tax_rates_local (
local_tax_id int(11) NOT NULL auto_increment COMMENT 'tax id',
zone_id int(11) default NULL COMMENT 'zen cart zone to apply tax',
local_fieldmatch varchar(100) default NULL COMMENT 'name of field from delivery table to match',
local_datamatch varchar(100) default NULL COMMENT 'Data to match delievery field',
local_tax_rate decimal(7,4) default '0.0000' COMMENT 'local tax rate',
local_tax_label varchar(100) default NULL COMMENT 'Label for checkout',
local_tax_shipping varchar(5) default 'false' COMMENT 'Apply this tax to shipping',
local_tax_class_id int(1) default NULL COMMENT 'Apply to products in what tax class',
PRIMARY KEY (local_tax_id)
) ENGINE=MyISAM AUTO_INCREMENT=1258 DEFAULT CHARSET=latin1 AUTO_INCREMENT=1258 ;
--
-- Dumping data for table tax_rates_local
INSERT INTO tax_rates_local (local_tax_id, zone_id, local_fieldmatch, local_datamatch, local_tax_rate, local_tax_label, local_tax_shipping, local_tax_class_id) VALUES (2, 18, 'postcode', '32601', 0.2500, 'Alachua', 'true', 1),
(3, 18, 'postcode', '32602', 0.2500, 'Alachua', 'true', 1),
(4, 18, 'postcode', '32603', 0.2500, 'Alachua', 'true', 1),
(5, 18, 'postcode', '32604', 0.2500, 'Alachua', 'true', 1),
(6, 18, 'postcode', '32605', 0.2500, 'Alachua', 'true', 1),
(7, 18, 'postcode', '32606', 0.2500, 'Alachua', 'true', 1),
(8, 18, 'postcode', '32607', 0.2500, 'Alachua', 'true', 1),
(9, 18, 'postcode', '32608', 0.2500, 'Alachua', 'true', 1),
(10, 18, 'postcode', '32609', 0.2500, 'Alachua', 'true', 1),
(11, 18, 'postcode', '32610', 0.2500, 'Alachua', 'true', 1),
(12, 18, 'postcode', '32611', 0.2500, 'Alachua', 'true', 1),
(13, 18, 'postcode', '32612', 0.2500, 'Alachua', 'true', 1),
(14, 18, 'postcode', '32613', 0.2500, 'Alachua', 'true', 1),
(15, 18, 'postcode', '32614', 0.2500, 'Alachua', 'true', 1),
(16, 18, 'postcode', '32615', 0.2500, 'Alachua', 'true', 1),
(17, 18, 'postcode', '32616', 0.2500, 'Alachua', 'true', 1),........
Thanks