February 23, 2005 at 12:57 pm
Basically, I have a situation where I am getting raw data from an Excel file that contains the following fields vendor_number, bus_name, add1, add2, city, state, zip. The data is brought into an initial table that has no constraints. I wish to move the data to a table with constraints (constraints being a concatenated primary key--both vendor_number and bus_name) through a DTS package. I am fine with this part, however, the data is repetitive and I want to get rid of the duplicates.
For example, if this is the data
001 Sally 24 west st
001 Sally 10 South street
001 Bert 24 west st
002 Eric 3 Ninth st
002 Bob 3 Ninth st
002 Bob 610 fifth st
For my purposes, I want distinct values for the combination of vendor_number and bus_name. If I run a select distinct vendor_number, bus_name from <table>, I get the results that I want--001 Sally, 001 Bert, 002 Eric, and 002 Bob. However, my difficulty comes into place because I need the rest of the data too--the remaining columns--add1, add2, city, state, zip. When I enter select distinct vendor_number, bus_name, add1, add2, city, state, zip, the result is every record listed above. When I attemp to insert this result into the new table, the constraints are violated--unique vendor_number and bus_name (it tries to enter both sally records, violating the constraints). Is there some subquery or filter that allows you to insert all of the fields into another table based on the uniqueness of the combo of two of those fields?
Any help is greatly appreciated. I am new to the whole coding thing and getting data from disparate sources.
Paula
February 23, 2005 at 1:08 pm
what is happening is that you are mixing things.
For example lets say you have determined that in the above data 002 and Bob are unique. Which address are you going to use ? the first , the last, ...
HTH
* Noel
February 23, 2005 at 1:14 pm
For my purposes, the address doesn't really matter--some of the data that will come in will have no address at all or addresses will be spelled differently or will be missing a zip code in one and not the other. So, basically, as long as I get the vendor_number, bus_name and any one of the addresses, it would be ok.
Paula
February 23, 2005 at 1:20 pm
>> So, basically, as long as I get the vendor_number, bus_name and any one of the addresses, it would be ok <<
Here you go then
select vendor_number, bus_name, Min(add1), Min(add2), Min(city), Min(state), Min(zip)
from TableName
group by vendor_number, bus_name
HTH
* Noel
February 23, 2005 at 1:20 pm
In the example data, there are 2 rows for vendor_number of 001 and bus_name of "Sally" with the addresss column having values of "24 west st" and "10 South street". Which one should be used?
Assuming the "last", try this solution. If the "first", change "MAX(InputOrder)" to "MIN(InputOrder)"
create table VendorAddress_input
( vendor_number integer
, bus_name varchar(255)
, add1 varchar(255)
, InputOrder bigint identity(1,1)
)
go
delete from VendorAddress_input
insert into VendorAddress_input
(vendor_number , bus_name, add1)
select 001, 'Sally','24 west st' union all
select 001, 'Sally','10 South street' union all
select 001, 'Bert','24 west st' union all
select 002, 'Eric','3 Ninth st' union all
select 002, 'Bob','3 Ninth st' union all
select 002, 'Bob','610 fifth st' union all
select 003, NULL ,'NO BusinessName 1' union all
select 003, NULL ,'NO BusinessName 2' union all
select null , 'Carl', 'NO Vendor 1' union all
select null , 'Carl', 'NO Vendor 2' union all
select null , null, 'NO Vendor or business name 1' union all
select null , null, 'NO Vendor or business name 2'
go
select VendorAddress_input.vendor_number
, VendorAddress_input.bus_name
, VendorAddress_input.add1
from VendorAddress_input
join (select MAX(InputOrder)
from VendorAddress_input
where VendorAddress_input.vendor_number is not null
and VendorAddress_input.bus_name is not null
group by VendorAddress_input.vendor_number
, VendorAddress_input.bus_name
  as VALast (InputOrder)
on VendorAddress_input.InputOrder = VALast.InputOrder
SQL = Scarcely Qualifies as a Language
February 23, 2005 at 1:25 pm
Thanks noeld. That works perfectly. Such a simple solution to a problem that has consumed quite a bit of my time.
Paula
February 23, 2005 at 1:31 pm
Such a simple solution to a problem that has consumed quite a bit of my time.
I have been there too, believe me Sometimes I need more coffee
* Noel
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply