Select where result are by the best match

20 Jan 2006 11:40 PM

Senna

Hi

Looking for a way to get the right product tax depending on the zip code.
The query should look something like below, but don't know how the expression
for the zip code matching should look like.

ISNULL(SELECT Tax FROM ProductTax WHERE ProductId = @productid AND CountryId
= @countryid AND Zip = [yeah, what goes here], 0)

Table
--------
CREATE TABLE [ProductTax] (
    [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
    [ProductId] [int] NOT NULL ,
    [CountryId] [int] NOT NULL ,
    [Zip] [nvarchar] (50) NOT NULL ,
    [Tax] [decimal](2, 0) NOT NULL ,
    CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
    (
        [Id]
    ) ON [PRIMARY]
) ON [PRIMARY]

Sample data
-------------
Id    ProductId    CountryId    Zip    Tax
1    23        2        *****    4.6
2    23        2        1234*    4.7
3    23        2        12345    4.8
4    23        2        123**    4,9
5    23        2        1****    5.0

Example: (CountryId + Best-matching-zip-code = Result)
--------------------------
CountryId    Zip    Result
23            4.6
23        12397    4.9
23        12345    4.8
23        12346    4.7
23        12355    4.9
23        23456    4.6
23        19999    5.0

Thanks.

20 Jan 2006 11:46 PM

Senna

Sample data.

INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2, *****,
4.6)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2, 1234*,
4.7)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2, 12345,
4.8)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2, 123**,
4.9)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2, 1****,
5.0)

21 Jan 2006 1:09 AM

Hilary Cotter

Have a look at Levenstein Edit Distance. There are tsql implementations.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"Senna" <Se***@discussions.microsoft.com> wrote in message
news:7D49563A-1DAB-43E2-A38C-5A438F36B953@microsoft.com...
> Hi
>
> Looking for a way to get the right product tax depending on the zip code.
> The query should look something like below, but don't know how the
> expression
> for the zip code matching should look like.
>
> ISNULL(SELECT Tax FROM ProductTax WHERE ProductId = @productid AND
> CountryId
> = @countryid AND Zip = [yeah, what goes here], 0)
>
>
> Table
> --------
> CREATE TABLE [ProductTax] (
> [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> [ProductId] [int] NOT NULL ,
> [CountryId] [int] NOT NULL ,
> [Zip] [nvarchar] (50) NOT NULL ,
> [Tax] [decimal](2, 0) NOT NULL ,
> CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
> (
> [Id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
>
>
> Sample data
> -------------
> Id ProductId CountryId Zip Tax
> 1 23 2 ***** 4.6
> 2 23 2 1234* 4.7
> 3 23 2 12345 4.8
> 4 23 2 123** 4,9
> 5 23 2 1**** 5.0
>
>
> Example: (CountryId + Best-matching-zip-code = Result)
> --------------------------
> CountryId Zip Result
> 23 4.6
> 23 12397 4.9
> 23 12345 4.8
> 23 12346 4.7
> 23 12355 4.9
> 23 23456 4.6
> 23 19999 5.0
>
>
> Thanks.

21 Jan 2006 1:43 AM

Alexander Kuznetsov

*untested*

select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
go
select top 1 * from(
select p.*,#n.n from ProductTax p, #n
where substring(zip,1, #n.n) = substring(@zip, 1, #n.n)
union all
select p.*,0 from ProductTax p
where zip is null)
order by n desc

21 Jan 2006 6:50 PM

Senna

Hi Alexander

Can't get it to work.

Thanks. / Senna

Show quote

"Alexander Kuznetsov" wrote:

> *untested*
>
> select 1 n
> into #n
> union all select 2
> union all select 3
> union all select 4
> union all select 5
> go
> select top 1 * from(
> select p.*,#n.n from ProductTax p, #n
> where substring(zip,1, #n.n) = substring(@zip, 1, #n.n)
> union all
> select p.*,0 from ProductTax p
> where zip is null)
> order by n desc
>
>

21 Jan 2006 8:44 PM

Alexander Kuznetsov

Senna,

here you go:

CREATE TABLE [ProductTax] (
        [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
        [ProductId] [int] NOT NULL ,
        [CountryId] [int] NOT NULL ,
        [Zip] [nvarchar] (50) NOT NULL ,
        [Tax] float NOT NULL ,
        CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
        (
                [Id]
        ) ON [PRIMARY]
) ON [PRIMARY]
go
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
'*****',
4.6)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
'1234*',
4.7)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
'12345',
4.8)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
'123**',
4.9)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
'1****',
5.0)
go
select
'12397' zip
into #sample
union all
select NULL zip
union all
select '12397' zip
union all
select '12345' zip
union all
select '12346' zip
union all
select '12355' zip
union all
select '23456' zip
union all
select '19999' zip
go
select 1 n
into #n
union all select 2
union all select 3
union all select 4
union all select 5
go
select distinct #sample.zip, p.zip MatchedToZip, p.tax
from #sample, ProductTax p
where p.zip =
(select top 1 zip from(
select p.*,#n.n from ProductTax p, #n
where substring(p.zip,1, #n.n) = substring(#sample.zip, 1, #n.n)
union all
select p.*,0 from ProductTax p
where zip = '*****') t
order by n desc )
order by #sample.zip

zip   MatchedToZip                                       tax

----- --------------------------------------------------
-----------------------------------------------------
NULL *****
4.5999999999999996
12345 12345
4.7999999999999998
12346 1234*
4.7000000000000002
12355 123**
4.9000000000000004
12397 123**
4.9000000000000004
19999 1****                                              5.0
23456 *****
4.5999999999999996

(7 row(s) affected)

go
drop table #sample
drop table #n
drop table ProductTax

21 Jan 2006 10:02 PM

Senna

Alexander, that worked great. A giant thank you for your time and knowledge.

/ Senna

Show quote

"Alexander Kuznetsov" wrote:

> Senna,
>
> here you go:
>
> CREATE TABLE [ProductTax] (
>         [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
>         [ProductId] [int] NOT NULL ,
>         [CountryId] [int] NOT NULL ,
>         [Zip] [nvarchar] (50) NOT NULL ,
>         [Tax] float NOT NULL ,
>         CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
>         (
>                 [Id]
>         ) ON [PRIMARY]
> ) ON [PRIMARY]
> go
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
> '*****',
> 4.6)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
> '1234*',
> 4.7)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
> '12345',
> 4.8)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
> '123**',
> 4.9)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax) VALUES(23, 2,
> '1****',
> 5.0)
> go
> select
> '12397' zip
> into #sample
> union all
> select NULL zip
> union all
> select '12397' zip
> union all
> select '12345' zip
> union all
> select '12346' zip
> union all
> select '12355' zip
> union all
> select '23456' zip
> union all
> select '19999' zip
> go
> select 1 n
> into #n
> union all select 2
> union all select 3
> union all select 4
> union all select 5
> go
> select distinct #sample.zip, p.zip MatchedToZip, p.tax
> from #sample, ProductTax p
> where p.zip =
> (select top 1 zip from(
> select p.*,#n.n from ProductTax p, #n
> where substring(p.zip,1, #n.n) = substring(#sample.zip, 1, #n.n)
> union all
> select p.*,0 from ProductTax p
> where zip = '*****') t
> order by n desc )
> order by #sample.zip
>
> zip   MatchedToZip                                       tax
>
> ----- --------------------------------------------------
> -----------------------------------------------------
> NULL *****
> 4.5999999999999996
> 12345 12345
> 4.7999999999999998
> 12346 1234*
> 4.7000000000000002
> 12355 123**
> 4.9000000000000004
> 12397 123**
> 4.9000000000000004
> 19999 1****                                              5.0
> 23456 *****
> 4.5999999999999996
>
> (7 row(s) affected)
>
>
> go
> drop table #sample
> drop table #n
> drop table ProductTax
>
>

21 Jan 2006 6:45 PM

Senna

Hi Hilary

Have now read about it at wikipedia.com and it seems to be just a little
above my head. But have searched the web without any luck of how to use it
within sql. So do you have any example or can point we anywhere for further
understanding.

Thanks. / Senna

Show quote

"Hilary Cotter" wrote:

> Have a look at Levenstein Edit Distance. There are tsql implementations.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Senna" <Se***@discussions.microsoft.com> wrote in message
> news:7D49563A-1DAB-43E2-A38C-5A438F36B953@microsoft.com...
> > Hi
> >
> > Looking for a way to get the right product tax depending on the zip code.
> > The query should look something like below, but don't know how the
> > expression
> > for the zip code matching should look like.
> >
> > ISNULL(SELECT Tax FROM ProductTax WHERE ProductId = @productid AND
> > CountryId
> > = @countryid AND Zip = [yeah, what goes here], 0)
> >
> >
> > Table
> > --------
> > CREATE TABLE [ProductTax] (
> > [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> > [ProductId] [int] NOT NULL ,
> > [CountryId] [int] NOT NULL ,
> > [Zip] [nvarchar] (50) NOT NULL ,
> > [Tax] [decimal](2, 0) NOT NULL ,
> > CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
> > (
> > [Id]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> >
> >
> > Sample data
> > -------------
> > Id ProductId CountryId Zip Tax
> > 1 23 2 ***** 4.6
> > 2 23 2 1234* 4.7
> > 3 23 2 12345 4.8
> > 4 23 2 123** 4,9
> > 5 23 2 1**** 5.0
> >
> >
> > Example: (CountryId + Best-matching-zip-code = Result)
> > --------------------------
> > CountryId Zip Result
> > 23 4.6
> > 23 12397 4.9
> > 23 12345 4.8
> > 23 12346 4.7
> > 23 12355 4.9
> > 23 23456 4.6
> > 23 19999 5.0
> >
> >
> > Thanks.
>
>
>

21 Jan 2006 10:33 PM

Senna

Ok, after some more searching I finally found some sql versions. For anyone
else that may be interested here are urls to two of them.
http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=584&lngWId=5
http://www.merriampark.com/ldtsql.htm

Show quote

"Hilary Cotter" wrote:

> Have a look at Levenstein Edit Distance. There are tsql implementations.
>
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Senna" <Se***@discussions.microsoft.com> wrote in message
> news:7D49563A-1DAB-43E2-A38C-5A438F36B953@microsoft.com...
> > Hi
> >
> > Looking for a way to get the right product tax depending on the zip code.
> > The query should look something like below, but don't know how the
> > expression
> > for the zip code matching should look like.
> >
> > ISNULL(SELECT Tax FROM ProductTax WHERE ProductId = @productid AND
> > CountryId
> > = @countryid AND Zip = [yeah, what goes here], 0)
> >
> >
> > Table
> > --------
> > CREATE TABLE [ProductTax] (
> > [Id] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
> > [ProductId] [int] NOT NULL ,
> > [CountryId] [int] NOT NULL ,
> > [Zip] [nvarchar] (50) NOT NULL ,
> > [Tax] [decimal](2, 0) NOT NULL ,
> > CONSTRAINT [PK_ProductTax] PRIMARY KEY CLUSTERED
> > (
> > [Id]
> > ) ON [PRIMARY]
> > ) ON [PRIMARY]
> >
> >
> > Sample data
> > -------------
> > Id ProductId CountryId Zip Tax
> > 1 23 2 ***** 4.6
> > 2 23 2 1234* 4.7
> > 3 23 2 12345 4.8
> > 4 23 2 123** 4,9
> > 5 23 2 1**** 5.0
> >
> >
> > Example: (CountryId + Best-matching-zip-code = Result)
> > --------------------------
> > CountryId Zip Result
> > 23 4.6
> > 23 12397 4.9
> > 23 12345 4.8
> > 23 12346 4.7
> > 23 12355 4.9
> > 23 23456 4.6
> > 23 19999 5.0
> >
> >
> > Thanks.
>
>
>

22 Jan 2006 6:23 PM

Senna

Hi again

The project will hold x stores, which will have their own set of products.
So it is possible that two products will have the same sku, but belong to
different stores. So the sku aren't unique.

I have the country code for the reason that for example here in Sweden we
only have country tax and not state/province/city tax. So the ideas goes like
this:

A company in Sweden would set it like this.
INSERT INTO ProductTax(ProductId, CountryId, Tax) VALUES (23, 1, 30);

But for customers in USA that buys from a company in Sweden the sales tax
would be set to 0.

And if a company in Sweden sells to another EU country, maybe Germany, for
more than SEK 320000, (I believe it is), then the product will include the
German tax instead, on further sales to Germany. And the owner of the store
have to add:
INSERT INTO ProductTax(ProductId, CountryId, Tax) VALUES (23, 5, 20);

And from what I understand, taxes in the USA can differ from
state/province/city to state/province/city.
So a company in USA could set a general tax like this:
INSERT INTO ProductTax(ProductId, CountryId, Tax) VALUES (23, 2, 4);

And if they have a office in Nebraska they would set the tax like this for
customers there:
INSERT INTO ProductTax(ProductId, CountryId, Tax) VALUES (23, 2, '68***', 5);
INSERT INTO ProductTaxes(ProductId, CountryId, Tax) VALUES (23, 2, '69***',
5);

What I don't know is if USA is the only country where the tax can differ
from state/province/city to state/province/city, by zip code in other words.
And if all other countries only have national taxes.

Is this the best solution, I doubt it, cause I have had a lot of problems on
how to deal with the tax issues.

So feel free to drop any ideas or correct me if I am way off on how the tax
rules work around the world.

Thanks / Senna.

22 Jan 2006 6:56 PM

--CELKO--

>> So feel free to drop any ideas or correct me if I am way off on how the tax rules work around the world. <<

I think I might want to go with one table per country, so you can have
the right combination of (country_code, postal_code, product_id) for
each of the horribly different situations in the world. For example, a
toy "action figure" has a "toy soldier" or "doll" tariff depending on
the country to which it is shipped. Then there are teh flat fees in
some cases. Etc.

I wonder if anyone has set up a devoted website and service for doing
this?

23 Jan 2006 3:13 AM

Alexander Kuznetsov

Hi Senna,

>>

What I don't know is if USA is the only country where the tax can
differ from state/province/city to state/province/city, by zip code in
other words.
<<

I'm not quite sure if you can rely on US zip code for your purposes. I
think some post offices sit right on the border between two states,
serving neighborhoods in two states. ANother possible complication: I
myself live in city of Naperville, Illinois. The city is located mostly
in DuPage county, but recently it sprawled into 2 other adjacent
counties. Since in Illinois both counties and cities can impose their
own taxes, the situation is not quite simple. I don't think you can
always figure out local sales tax based on zip code only.

However, I'm not sure you need to deal with local sales taxes either.
Hopefully state taxes would be enough.

Good luck!

23 Jan 2006 1:01 PM

Senna

Hi again

Have slept on it now and its still pretty confusing. But I can't dwell on it
forever so I will go with what I got. And I have from the beginning been
aware of that it would be impossible to support every scenario out there. But
when being able to set the sales tax by postal codes, which most countries
have, I may be able to support the majority of cases that have area based
taxation.

I as a developer don't need to care about taxes bundling up on eachother
thats up to each storeowner to set the right tax. But I'm at least giving
them some possibility to override the national tax for more area based
taxation. Most stores will probably only have national taxes and maybe state
taxes. Just have to inform them about it being a little bit tricky or
impossible in some cases.

And as a conclusion I feel pretty satisfied with Alexanders second solution.
Its seems to do the trick in most cases. So if Alexander doesn't find any
errors or have any improvements of the sql, I will go with the sql you posted
before.

Thanks to everyone for all of your input. Have been interesting.

/Senna

Show quote

"Alexander Kuznetsov" wrote:

> Hi Senna,
>
> >>
> What I don't know is if USA is the only country where the tax can
> differ from state/province/city to state/province/city, by zip code in
> other words.
> <<
>
> I'm not quite sure if you can rely on US zip code for your purposes. I
> think some post offices sit right on the border between two states,
> serving neighborhoods in two states. ANother possible complication: I
> myself live in city of Naperville, Illinois. The city is located mostly
> in DuPage county, but recently it sprawled into 2 other adjacent
> counties. Since in Illinois both counties and cities can impose their
> own taxes, the situation is not quite simple. I don't think you can
> always figure out local sales tax based on zip code only.
>
> However, I'm not sure you need to deal with local sales taxes either.
> Hopefully state taxes would be enough.
>
> Good luck!
>
>

24 Jan 2006 3:58 AM

--CELKO--

>> Hopefully state taxes would be enough. <<

No, not even close in the USA. For example, the state of Georigia has
159 counties, then within the county, they can have townships with
different liquor laws. They are all different. Example; Atlanta has
a 1% "MARTA" (Metro Atlanta Rapid Transportation Authority) sales tax.

22 Jan 2006 1:42 AM

--CELKO--

Never use IDENTITY for a key! This is basic RDBMS design. Your data
has no integrity as now written. Next, only the United States has ZIP
codes, so why use a country code? And if you use it, then why not use
the ISO standard?

CREATE TABLE ProductTaxes
(product_id INTEGER NOT NULL, -- ean? upc? sku?
start_zip_code CHAR (5) NOT NULL,
end_zip_code CHAR (5) NOT NULL,
CHECK (start_zip_code <= end_zip_code),
tax_rate DECIMAL(5,3) NOT NULL,
PRIMARY KEY (product_id, start_zip_code));

Use a simple BETWEEN predicate in your query. Notice that your sample
data has overlaps. This one covers everything.

INSERT INTO ProductTaxes VALUES (23, 00000, 99999, 4.6);

This one covers parts of Texas:

INSERT INTO ProductTaxes VALUES (23, 78700, 78799, 4.7);

Did you mean to have multiple taxes apply to the product?

22 Jan 2006 4:41 AM

Alexander Kuznetsov

my understanding is (and I might be wrong) that in there is a federal
tax, there might be a state/province tax on top of the federal one, and
possibly a county/city taxes on top of that. Is that is the case?

I see that the line
5 23 2 1**** 5.0
apparently contradicts with my assumption

22 Jan 2006 6:16 PM

--CELKO--

>> taxes on top of that. Is that is the case? <<

Oh, yes. Taxes are crazy and can really pile up by federal, county,
city levels, then be modified by what the product is. For example, in
some states have no taxes on food and Bibles. There is a federal
"luxury tax" that destroyed the American yacht industry, etc.

The Zip code ranges ought to be the full nine-digit ZIP+4 to allow for
city borders. And we will need a "price * SUM(tax_rate)" computation
in the query.

22 Jan 2006 11:38 AM

Tony Rogerson

> Never use IDENTITY for a key! This is basic RDBMS design. Your data

Wrong! Basic RDBMS design is for your primary key of the table to be the
'natural key' of the entity you are modelling into a table.

It might be that there is no industrial 'natural key', for instance a
message board, membership numbers in a user group, soceity etc...

You can use the IDENTITY property to create a key, you can also build check
digits around the value the IDENTITY property returned.

In your example below, ProductTaxes, note under the ISO standard you should
be using singular lower case identifier names for a start, but the
product_id may well not have an industrial standard because it is simply a
meta table with a list of taxes.

Are you sure the PRIMARY KEY is correct? You are assuming a lot there.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1137894173.155164.208400@g14g2000cwa.googlegroups.com...
> Never use IDENTITY for a key! This is basic RDBMS design. Your data
> has no integrity as now written. Next, only the United States has ZIP
> codes, so why use a country code? And if you use it, then why not use
> the ISO standard?
>
> CREATE TABLE ProductTaxes
> (product_id INTEGER NOT NULL, -- ean? upc? sku?
> start_zip_code CHAR (5) NOT NULL,
> end_zip_code CHAR (5) NOT NULL,
> CHECK (start_zip_code <= end_zip_code),
> tax_rate DECIMAL(5,3) NOT NULL,
> PRIMARY KEY (product_id, start_zip_code));
>
> Use a simple BETWEEN predicate in your query. Notice that your sample
> data has overlaps. This one covers everything.
>
> INSERT INTO ProductTaxes VALUES (23, 00000, 99999, 4.6);
>
> This one covers parts of Texas:
>
> INSERT INTO ProductTaxes VALUES (23, 78700, 78799, 4.7);
>
> Did you mean to have multiple taxes apply to the product?
>

22 Jan 2006 4:32 PM

Alexander Kuznetsov

I think it would be better to store the facts like this:

INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
VALUES(23, 2,
'%',
4.6,0)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
VALUES(23, 2,
'1234%',
4.7,4)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
VALUES(23, 2,
'12345',
4.8,5)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
VALUES(23, 2,
'123%',
4.9,3)
INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
VALUES(23, 2,
'1%',
5.0,1)

(Priority being the number of bytes before percent sign).
Then the select is simpler (untested)

select distinct #sample.zip, p.zip MatchedToZip, p.tax
from #sample, ProductTax p
where p.zip =
(select top 1 zip from(
select p.* from ProductTax p
where #sample.zip like p.zip) t
order by priority desc )
order by #sample.zip

I think there is no need to provide intervals of zip codes, as under
the hood SQL Server rewrites

where p.zip like '1%'

to

p.zip between '1 ' and '1ZZZZZZ'

where Z is the biggest character for the current collation. This way
the code will work with international zip codes, and I guess that's
necessary because of the presence of CountryId column and because of
nvarchar type of zip column.

22 Jan 2006 7:10 PM

--CELKO--

But the problem with mixed postal codes is that you need a CHECK()
constraint from Hell:

CHECK (CASE country_code
              WHEN 'US' AND postal_code LIKE
'[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
               THEN 'T'
              WHEN 'CA' AND postal_code LIKE
'[0-9][A-Z][0-9]-[A-Z][0-9][A-Z]'
               THEN 'T'
                etc. for all of the nations to which we ship.
               ELSE 'F' END = 'T')

There is a regular expression for the UK system that is about 8 lines
long. I do not know of any postal code that is not alphanumeric, with
a dash as punctuation.

22 Jan 2006 7:50 PM

Tony Rogerson

No UK postal codes have a dash in them.

This is where SQL fails and we can need to go out to CLR to work with
powerful value validation, luckily we can use the UDF inside the CHECK
constraint rather than have the constraint from Hell.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1137957020.266985.10140@o13g2000cwo.googlegroups.com...
> But the problem with mixed postal codes is that you need a CHECK()
> constraint from Hell:
>
> CHECK (CASE country_code
>              WHEN 'US' AND postal_code LIKE
> '[0-9][0-9][0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'
>               THEN 'T'
>              WHEN 'CA' AND postal_code LIKE
> '[0-9][A-Z][0-9]-[A-Z][0-9][A-Z]'
>               THEN 'T'
>                etc. for all of the nations to which we ship.
>               ELSE 'F' END = 'T')
>
> There is a regular expression for the UK system that is about 8 lines
> long. I do not know of any postal code that is not alphanumeric, with
> a dash as punctuation.
>

22 Jan 2006 7:48 PM

Tony Rogerson

Hi Alexander,

Take the UK, we don't have area based taxation, some products are zero VAT,
most consumer goods are 17.5%, food is taxed depending on whether its
cooked - quite a few combinations.

Not sure how you would fit that in, be a real pain to enter all the post
codes in the UK, not sure that is doable anyway because new postcodes spring
up often as new housing estates are created, a post code here pin points the
house to within half a dozen or so. Take mine, AL5 4LA; AL is St Albans, 5
is Harpenden and the 4LA is area and a road within Harpenden.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1137947530.839212.29910@g14g2000cwa.googlegroups.com...
>I think it would be better to store the facts like this:
>
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
> VALUES(23, 2,
> '%',
> 4.6,0)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
> VALUES(23, 2,
> '1234%',
> 4.7,4)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
> VALUES(23, 2,
> '12345',
> 4.8,5)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
> VALUES(23, 2,
> '123%',
> 4.9,3)
> INSERT INTO ProductTax(ProductId, CountryId, Zip, Tax, Priority)
> VALUES(23, 2,
> '1%',
> 5.0,1)
>
> (Priority being the number of bytes before percent sign).
> Then the select is simpler (untested)
>
> select distinct #sample.zip, p.zip MatchedToZip, p.tax
> from #sample, ProductTax p
> where p.zip =
> (select top 1 zip from(
> select p.* from ProductTax p
> where #sample.zip like p.zip) t
> order by priority desc )
> order by #sample.zip
>
> I think there is no need to provide intervals of zip codes, as under
> the hood SQL Server rewrites
>
> where p.zip like '1%'
>
> to
>
> p.zip between '1 ' and '1ZZZZZZ'
>
> where Z is the biggest character for the current collation. This way
> the code will work with international zip codes, and I guess that's
> necessary because of the presence of CountryId column and because of
> nvarchar type of zip column.
>

23 Jan 2006 3:28 AM

Alexander Kuznetsov

Hi Tony,

>>

we don't have area based taxation, some products are zero VAT,
most consumer goods are 17.5%, food is taxed depending on whether its
cooked - quite a few combinations.
<<

I would either start with something like this:

create table TaxCategoryArea
(
CountryID int,
LocationPattern varchar(20),
CategoryName varchar(20),
TaxRate float
)

populating it like this, with % as the pattern:

insert into TaxCategoryArea
values(44,'%','Grocery',0.175)

--- since we need to dial 44 to call our friends in the UK

--- assuming that we don't have do deal with county/city taxes in the
US:

insert into TaxCategoryArea
values(1,'%','Grocery',0.0)
--- no federal sales tax for the US
insert into TaxCategoryArea
values(1,'IL','Grocery',0.0675)
--- 6.75% sales tax in Illinois - almost 3 times lower then back there
in the UK!

--- no rows for Texas if it does not have its own sales tax

What do you think?

23 Jan 2006 8:05 AM

Tony Rogerson

Its one way Alexander, whatever way you do it you are always going to have
to differentiate between countries that have tax regimes based on area and
ones based across the country.

Personally I'd probably go with something like what you've done and use a
scalar value UDF with country and location/area to work out the tax, you can
then put the country logic into the UDF. Nice and clean then, if you didn't
do it like that you'd have to use complex joins on all your queries, well,
more than your basic join anyway.

Tony

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials

Show quote

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in message
news:1137986883.281659.249030@f14g2000cwb.googlegroups.com...
> Hi Tony,
>
>>>
> we don't have area based taxation, some products are zero VAT,
> most consumer goods are 17.5%, food is taxed depending on whether its
> cooked - quite a few combinations.
> <<
>
> I would either start with something like this:
>
> create table TaxCategoryArea
> (
> CountryID int,
> LocationPattern varchar(20),
> CategoryName varchar(20),
> TaxRate float
> )
>
> populating it like this, with % as the pattern:
>
> insert into TaxCategoryArea
> values(44,'%','Grocery',0.175)
>
> --- since we need to dial 44 to call our friends in the UK
>
> --- assuming that we don't have do deal with county/city taxes in the
> US:
>
> insert into TaxCategoryArea
> values(1,'%','Grocery',0.0)
> --- no federal sales tax for the US
> insert into TaxCategoryArea
> values(1,'IL','Grocery',0.0675)
> --- 6.75% sales tax in Illinois - almost 3 times lower then back there
> in the UK!
>
> --- no rows for Texas if it does not have its own sales tax
>
> What do you think?
>