Merging two queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Merging two queries

Author

Topic

Jammericon
Starting Member

2 Posts

Posted - 2006-12-11 : 11:05:12

I'm new to the forum so I apologize if this is a redundant post.

I have a tax table that has zip codes, City, default tax authority, and rates. (i.e. ZIP, CITY, DTA, Rate). There can be multiple Rates for a ZIP code and possible to have a default if the ZIP City combo doesn't exist. If no DTA is defined, then there is only one Zip code/Rate.
I have been trying to write a query that will give me the DTA if it exists and if the DTA is not defined, then the single zip/rate.

A "SELECT DISTINCT ZIP FROM Taxes" Will give me a list of all the zips. If I include the tax rate, it gives me dups of those zips that have a DTA.
A "SELECT * FROM Taxes WHERE DTA='T'" gives me all the zips that have a DTA but not the zip codes that only have one tax rate associated to them.

Since the taxe rates come from a 3rd party, I don't really want to mess with the data (i.e. put a DTA for those zip codes that only have one tax rate).

Any suggestions would be greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 16:08:29

It would be much easier if you could provide some sample data, your expected output based on the provided sample data. Some DDL would be nice.

Peter Larsson
Helsingborg, Sweden

Jammericon
Starting Member

2 Posts

Posted - 2006-12-12 : 08:50:05

Here is some sample data:
ZIP City DTA Rate
90001 Firestone .0825
90001 Firestone Pk .0825
90001 Los Angeles T .0825
90002 Los Angeles .0825
90002 Watts T .0825
90003 Los Angeles .0825
90004 Los Angeles T .0825
90004 Oakwood .0825

While the rate is the same in this example, it is not always the case. As you will notice, 90003 only has one entry for that postal code.

Any help you can provide would be greatly appreciated.

Thanks,
Jim

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 17:10:03

Bad sample. Give some example where the Rate is different.
Also provide you expected output based on the new sample data.

If you are serious, you will provide the sample data in this format

declare @test table (ZIP int, City varchar(11),  DTA varchar(2), Rate smallmoney)

insert	@test
select	90001, 'Firestone', NULL, .0825 union all
select	90001, 'Firestone', 'Pk', .0825 union all
select	90001, 'Los Angeles', 'T', .0825 union all
select	90002, 'Los Angeles', NULL, .0825 union all
select	90002, 'Watts', 'T', .0825 union all
select	90003, 'Los Angeles', NULL, .0825 union all
select	90004, 'Los Angeles', 'T', .0825 union all
select	90004, 'Oakwood', NULL, .0825

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources