Finding duplicate entries in a "smart" way - by comparing first two words

What is the best way to compare two entries in a single table where
the two fields are "almost" the same?

For example, I would like to write a query that would compare the
first two words in a "company" field. If they are the same, I would
like to output them.

For example, "20th Century" and "20th Century Fox" in the company
field would be the same.

How do I do this? Do I need to use a cursor? Is it as simple as using
"Like?"Here's an example:

CREATE TABLE Sometable (company VARCHAR(20) PRIMARY KEY)

INSERT INTO Sometable VALUES ('20th Century')
INSERT INTO Sometable VALUES ('20th Century Fox')
INSERT INTO Sometable VALUES ('Something Else')

SELECT company
FROM Sometable AS S
JOIN
(SELECT first_two_words
FROM
(SELECT company,
LEFT(company,
CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))
AS first_two_words
FROM Sometable) AS X
GROUP BY first_two_words
HAVING COUNT(*)>1) AS T
ON T.first_two_words =
LEFT(company,CHARINDEX(' ',company+' ',CHARINDEX(' ',company)+1))

--
David Portas
----
Please reply only to the newsgroup
--|||>> a query that would compare the first two words in a "company"
field [sic]. If they are the same, I would like to output them. <<

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY,
company CHAR(35) NOT NULL,
...);

SELECT F1.foo_key, F2.foo_key
FROM Foobar AS F1, Foobar AS F2
WHERE CHARINDEX(F1.company, F2.company) = 1
AND F1.foo_key < F2.foo_key;

Find value match in SQL table

Find value match in SQL table

Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?

Taod for ms sql server has that feature to compare rows from 2 tables..

I nkow that are theare also others but Toad i did use it..

Sorry for my bad english

|||

There is a tablediff.exe that comes with SQL Server 2005, if you are using 2005.

find # of days passed

Hello,

I need to take one of my date column and compare it with the current date and see how many day difference? How can I do this?

Thanks,

SELECT
DATEDIFF( day, myDateColumn, GetDate()
FROM
myTable
DATEDIFF is a built in sql function, the first parameters tells what "value" to return,
day = number of days,
the second two parameters tell it what dates to make calculations against.
bill

|||in some function...
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(date2.Ticks - date1.Ticks);
}
..i didn't test for negative, so if you just want the difference (where could be that 1 < 2 or 2 < 1) then maybe something like
public TimeSpan DateDifference(DateTime date1, DateTime date2)
{
return new TimeSpan(Math.Abs(date2.Ticks - date1.Ticks));
}
|||

You need the DATEDIFF function, if you write VB you are in luck but it is not available in C# so you can use the T-SQL version. Try the links below to get started. Hope this helps.

http://www.stanford.edu/~bsuter/sql-datecomputations.html
http://www.15seconds.com/issue/041013.htm

|||I can bring it as a new column, however main objective is to some amounts
and give three totals such as "Current Amount", "30-60 days amount", "> 60
days amount". How can I do this in Reporting Services?
Thanks,
|||This will pull each of the aging into its own column. myDateColumn is the date you want to check. When the DateDiff is in given amount, the amount will be summed. When it falls outside the (ie < 30 ) range, it will sum 0.
Select
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) < 30 Amount else 0 End ) as Curent,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) between 30 and 60 Amount else 0 End ) as Thirty,
Sum( Case When DATEDIFF( day, myDateColumn, GetDate() ) > 60 Amount else 0 End ) as Over
...
'rest of your query.
bill|||

Thank you very much for your help. Is it possible to this in RS, My query is already so complex, I am trying to see if I can do it in RS.

|||Are you looking for a "check box" that will do this for you? I do not believe one exists.
bill|||

:-)))). I am sure there is a way to do it in RS, there are lots of ability to write code there, I did not get training yet, that is why I am asking. Have you ever written a function in Code window? Is there a good reference that can explain it to me.