473,322 Members | 1,421 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Tricky SQL question

Ok so I'm gonna attempt to explain my problem as easy as I can. I have
a database of contacts (they are actually vendors). I am writing a
procedure that will query the vendors and generate a tab delimited file
that will be used an accounting program (MYOB). In the vendors database
I am exporting from, I have a field for "First Name", a field for "Last
Name" and a field (looked up by ID) for "Company Name". Now here is the
problem. In the usual case there will be a company name, a first name
and a last name for example:

Company Name: ACME Corporation
First Name: John
Last Name: Smith

HOWEVER, sometimes the vendor is not part of a company and the users of
the database have just been entering in the vendors name as the name of
the company. So it would end up looking like this:

Company Name: John Smith
First Name: John
Last Name: Smith

So the problem is that the accounting program that I am importing these
contacts into uses a weird schema. Instead of separate fields for first
name, last name and company, they have a combined field for "Company
Name/Last Name" and then a separate field for first name. So if the
first name field is blank then we know this contact is a company and
not an individual. As soon as we provide a first name, the record
becomes an individual as opposed to a company.

SO the MYOB import procedure is expecting two fields. One called
"[Co./Last Name]" and one called "[First Name]". So when I query my
current vendor database, I need to figure out if the company name
contains both the first and last name of the vendor and if so then
return only the vendors "last name" for the "[Co./Last Name]" field.
However if the company name is completely different then the first and
last name of the vendor, then I need to return the company name for
the "[Co./Last Name]" field and make the "[First Name]" field blank.

Here is what I thought would work but it doesn't:

SELECT
CASE
WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyName
+ '%'
THEN cu.vchrLastName
ELSE co.vchrCompanyName
END AS [Co./Last Name],
CASE
WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName
+ '%'
AND cu.vchrLastName LIKE '%' + co.vchrCompanyName
+ '%'
THEN cu.vchrFirstName
ELSE ''
END AS [First Name],
I don't get an error when I use this code, but it just always fails and
ends up using the company name no matter what even though I know for
sure that the first and last names ARE in the company name.

Sorry for my long winded explanation.

THANKS

Feb 22 '06 #1
5 1731
Erich93063 (er********@gmail.com) writes:
I have a field for "First Name", a field for "Last Name" and a field
(looked up by ID) for "Company Name". Now here is the problem. In the
usual case there will be a company name, a first name and a last name
for example:

Company Name: ACME Corporation
First Name: John
Last Name: Smith

HOWEVER, sometimes the vendor is not part of a company and the users of
the database have just been entering in the vendors name as the name of
the company. So it would end up looking like this:

Company Name: John Smith
First Name: John
Last Name: Smith

So the problem is that the accounting program that I am importing these
contacts into uses a weird schema. Instead of separate fields for first
name, last name and company, they have a combined field for "Company
Name/Last Name" and then a separate field for first name. So if the
first name field is blank then we know this contact is a company and
not an individual. As soon as we provide a first name, the record
becomes an individual as opposed to a company.
...
SELECT CASE WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName + '%' AND
cu.vchrLastName LIKE '%' + co.vchrCompanyName + '%'
THEN cu.vchrLastName
ELSE co.vchrCompanyName
END AS [Co./Last Name],
CASE WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName + '%' AND
cu.vchrLastName LIKE '%' + co.vchrCompanyName + '%' THEN
cu.vchrFirstName
ELSE ''
END AS [First Name],


You have it backwards: You are trying to see if "John" is like
%John Smith% which it isn't. But "John Smith" is like "%John%".

Really how you should do this, I don't know, because I suspect that
the quality of the data is not perfect. It it was, you should
just say:

CASE WHEN FirstName + ' ' + LastName = CompanyName

But users may have entered extra spaces between the first and last
name - or no space at all.

You could try

CASE WHEN CompanyName LIKE '%' + FirstName '%' AND
CompanyName LIKE '%' + LastName '%'

But there is entry where the users entered X first and last name, and
the company has an X in the name...

It would have been a lot simpler, if you had permitted CompanyName
to be NULL for private vendros.

By the way, in the output, shouldn't {First Name] be NULL rather than
'' for companies?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 22 '06 #2
OMG Im stupid. Ok yeah so now it works perfectly! THANKS

As far as the first name being NULL or not, I am generated a CSV file
so I just need an empty string. Unless you're saying it is more optimal
to use NULL instead of an empty string. As far as what would get
outputed to the CSV file it would be the same.

THANK YOU SO MUCH!!!!!!!!!!!!

Feb 22 '06 #3

"Erich93063" <er********@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Ok so I'm gonna attempt to explain my problem as easy as I can. I have
a database of contacts (they are actually vendors). I am writing a
procedure that will query the vendors and generate a tab delimited file
that will be used an accounting program (MYOB). In the vendors database
I am exporting from, I have a field for "First Name", a field for "Last
Name" and a field (looked up by ID) for "Company Name". Now here is the
problem. In the usual case there will be a company name, a first name
and a last name for example:

Company Name: ACME Corporation
First Name: John
Last Name: Smith

HOWEVER, sometimes the vendor is not part of a company and the users of
the database have just been entering in the vendors name as the name of
the company. So it would end up looking like this:

Company Name: John Smith
First Name: John
Last Name: Smith


Unless I've misundestood what you're describing - the thing you absolutely
have to address is the data issues. It's a hoary old truism - but, GIGO.
In situations like this - I have always found that you will get a
significant number of entries which are duplicates. Eg - If you have John
Smith, Dave Jones and Min Patel from the same company - you may well have at
least 3 entries. If you have John Smith from several - perhaps competing
companies, or as individuals, you have a serious mess.
If you are trying to report on this data - you're feeding your decision
makers crap.
In the first instance - you have to try and get high level buy in to put
significant pressure on those entering the data to clean it and keep it
clean. This may involve a change to the interface at the front end - for
example, to force the user to enter a postcode/zip code for any new entry,
then next option is to choose from an entry already in the database - and
then - if it's not there - to allow them to select an option from a
commercial address lookup such as the PAF software available in the UK -
where you have to do a search linked to zip/post code.

All of this - obviously - has recource issues. You have to make it clear
that no matter how clever you try to be - you can't address shite data with
increasingly complex workrounds. A chaotic contacts database loses the
company money in a load of ways. If you claim that you can guesstimate what
people "should" have put in - the first time you fail on a juicy contract -
you're in the crap.

Your only professional and sensible option is to go to the people who are
living by this information to feed their families - get buy in and get the
people putting crap in to put it right, get agreement to implement a
strategy for ensuring that the data is useful, getting agreement that this
is stuck to.

Contacts are silver - leads are gold - you have to make sure they're usable
and tracable. Getting in touch with the wrong Mr Smith or Mr Singh is not
acceptable. Therefore, trying to address the issue by any other means than
a data cleaning exercise involving those putting in crap is not a good
option. Thwe ymay be putting in rubbish - but I bet they know who 90% + of
the people are. End users think computers can make heuristic decisions
based on their phone contacts with the customer - ot that yo can program
them. If you persist this myth - it'll get worse.

I'll be honest - an attempt at a workaround is easier to sell - but in this
case you really have to take the hard road and sell other people who know
who is who putting it right and knowing they have to keep it right. It's
not going to make you popular and by the time your company is making money
on it - you'll be forgotten. You'll be entitled to sit back and feel smug
though (;->

Summary;
There is *no* work around for unusable data. The better the data quality -
the more money your company makes. Mistakes on your on your contacts
database can be "very" embarassing and lose money - if you sell a
workaround, *you* are in the firing line. Get the data right. This helps
people using the data make money. The more they make - the more chance of
keeping your job - or getting the pay rise you deserve. This is a
management issue - *not* a technical one.

IMHO

All the best
Andrew
Feb 22 '06 #4
Erich93063 (er********@gmail.com) writes:
As far as the first name being NULL or not, I am generated a CSV file
so I just need an empty string. Unless you're saying it is more optimal
to use NULL instead of an empty string. As far as what would get
outputed to the CSV file it would be the same.


OK, for a CSV that would be the right thing. I got the impression that
you were inserting data into a table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 23 '06 #5
Andrew (an***********@Nospamthanks.com) writes:
Contacts are silver - leads are gold


Lead into gold? Alchemy?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 23 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: JZ | last post by:
Oracle 9iR2 I have a table: SQL> select * from test; A B C ------------------- ---------- ---------- 01/01/2004 10:00:00 1 1...
3
by: Martin | last post by:
Dear Group I wonder whether you can push me in a direction on how to design the following statement. I'm looking for a SELECT with some tricky ORDER BY. The database table looks like this: ...
1
by: Tim | last post by:
Hello, I'm extremely puzzled; I cannot figure out what I'm doing wrong. Here's the situation. I would really appreciate any suggestions. I'm modifying a shopping cart in the following way....
2
by: Kennedy_f | last post by:
Most questions of exam 70-228 have a selection of answers that all seem correct, but in reality, the right answer is the one that best solves the question.There a few trick questions like how to...
0
by: Piotr Szukalski | last post by:
Hi! I have a quite tricky question about .NET debugger: do I need to install the whole SDK to make SDK CLR debugger working? The situation is as follows: I have an application deployed to 130...
25
by: PyPK | last post by:
What possible tricky areas/questions could be asked in Python based Technical Interviews?
8
by: pras.vaidya | last post by:
Hi , below given question was asked to me during an interview and i figured it out little tricky . It would be a great help if anyone could solve it. Code : - main() { char...
2
by: Mark Sandfox | last post by:
I have a tricky control validation issue. I have probably designed this the wrong way but here is what I have. I have 6 TextBoxes; tbPN, tbA, tbC, tbS, tbZ, tbDOB and there are 20 of each with...
14
by: felixnielsen | last post by:
Consider this 3d vector: const SIZE = 'some_size'; std::vector<std::vector<std::vector<char> > >GRID(SIZE, std::vector<std::vector<char> >(SIZE, std::vector<char>(SIZE))); It can be viewed...
9
by: howachen | last post by:
Hi, I have one very simple tricky question which is quite interesting, I would like to share with all of you here... //======================================= <script...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.