Finding duplicate records using two columns

I'm trying to find duplicate records in a table. I know how to do that using
a single column value, but I need to do it using the combined values of two
columns, company_id and client_id. There multiple are records with the same
company_id value (e.g., 123) and multiple records with the same department_id
value (e.g., 456). But there should be only one record with a company_id
value of 123 and a department_id value of 456.
I've searched for data in one table that is not in another table on these
two columns using CAST to concatenate the values:
select * from company c
where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
not in
(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
from client r)
But I can't seem to figure out how to use CAST to find duplicate records in
a single table. Should I be doing this a different way?
Any and all help would be appreciated.
JohnSELECT company_id, client_id, count(*) as Duplicates
FROM Company
GROUP BY company_id, client_id
HAVING COUNT(*) > 1
This works great with one column or ten columns.
Roy Harvey
Beacon Falls, CT
On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
<moderndads(nospam)@.hotmail.com> wrote:
>I'm trying to find duplicate records in a table. I know how to do that using
>a single column value, but I need to do it using the combined values of two
>columns, company_id and client_id. There multiple are records with the same
>company_id value (e.g., 123) and multiple records with the same department_id
>value (e.g., 456). But there should be only one record with a company_id
>value of 123 and a department_id value of 456.
>I've searched for data in one table that is not in another table on these
>two columns using CAST to concatenate the values:
>select * from company c
>where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
>not in
>(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
>from client r)
>But I can't seem to figure out how to use CAST to find duplicate records in
>a single table. Should I be doing this a different way?
>Any and all help would be appreciated.
>John|||Thank you, Roy! It worked perfectly.
John
"Roy Harvey (SQL Server MVP)" wrote:
> SELECT company_id, client_id, count(*) as Duplicates
> FROM Company
> GROUP BY company_id, client_id
> HAVING COUNT(*) > 1
> This works great with one column or ten columns.
> Roy Harvey
> Beacon Falls, CT
> On Tue, 30 Oct 2007 12:56:05 -0700, John Steen
> <moderndads(nospam)@.hotmail.com> wrote:
> >I'm trying to find duplicate records in a table. I know how to do that using
> >a single column value, but I need to do it using the combined values of two
> >columns, company_id and client_id. There multiple are records with the same
> >company_id value (e.g., 123) and multiple records with the same department_id
> >value (e.g., 456). But there should be only one record with a company_id
> >value of 123 and a department_id value of 456.
> >
> >I've searched for data in one table that is not in another table on these
> >two columns using CAST to concatenate the values:
> >
> >select * from company c
> >where cast(c.company_id as varchar(20)) + cast(c.client_id as varchar(20))
> >not in
> >(select cast(r.company_id as varchar(20)) + cast(r.client_id as varchar(20))
> >from client r)
> >
> >But I can't seem to figure out how to use CAST to find duplicate records in
> >a single table. Should I be doing this a different way?
> >
> >Any and all help would be appreciated.
> >
> >John
>

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;

Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advanceKevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Finding CPU usage by database on a server

We often have many databases on a single SQL Server instance. Is there a
simple way to determine resource usage (mainly CPU) on a per database basis?
For example, we have databases A through Z. I want to be able to determine
that database A is taking up 5% of the CPU, while Y is taking up 95%.
Anybody have any experience along these lines?
Thanks in advance
Kevin Lavelle wrote:
> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
If your users tend be in a single database for the duration of their
connections, the easiest way is to capture the Disconnect event from
Profiler which aggregates all the CPU, Duration, Reads, and Writes for
the connection. A simple query can total everything up by database
(Database ID).
If that's not the case, and users tend to switch databases a lot, the
second easiest way is to capture the SQL:BatchCompleted and
RPC:Completed events (leave TextData out and capture only performance
related columns and the DatabaseID). You can then total up the results
by SQL execution and group by database id.
In either case, using a server-side trace is better. But for the first
option, using Profiler may be sufficient.
David Gugick
Imceda Software
www.imceda.com
|||It is not easily achievable. You may profile the SQL server and look at the
CPU column based on grouping of databases. But that is very crude and rough
estimate.
"Kevin Lavelle" <kevinlavelle@.community.nospam> wrote in message
news:BA3B738C-3745-41D5-9CA6-4C264367BEFF@.microsoft.com...
> We often have many databases on a single SQL Server instance. Is there a
> simple way to determine resource usage (mainly CPU) on a per database
> basis?
> For example, we have databases A through Z. I want to be able to
> determine
> that database A is taking up 5% of the CPU, while Y is taking up 95%.
> Anybody have any experience along these lines?
> Thanks in advance
|||Hi Kevin,
One of our products within the Quest Central suite, Performance Analysis, does exactly this. Plus, it also gives you performance information on a variety of other dimensions, such as application, tables, filegroups, etc. Check it out at http://www.quest
.com/quest_central_for_sql_server/index.asp.
There's a free trial version available on the website.
HTH,
-Kevin Kline
Quest Software (www.quest.com)
SQL Server MVP
I support PASS, the Professional Association for SQL Server. (www.sqlpass.org)

> We often have many databases on a single SQL Server instance. Is
> there a simple way to determine resource usage (mainly CPU) on a per
> database basis?
> For example, we have databases A through Z. I want to be able to
> determine that database A is taking up 5% of the CPU, while Y is
> taking up 95%. Anybody have any experience along these lines?
> Thanks in advance
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
Kieran
Kidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
> ) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >= @.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:
>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>sql

Find what variations are searched by the freetext query

I am using the Freetext predicate to search a column to search for phrases
and variations on phrases.
So say I just use a single phrase to search on e.g. 'enquiry' it will return
variations such as 'enquire', 'enquiry', 'enquires' etc. This works fine.
What I would like, is when I display the results to highlight the variation
a little like Google groups.
If you can imagine I have enquiry it a returned block of text it will be
highlighted. This would be pretty straight forward if the only returned word
was enquiry as I could find that. But what about the variations. Is there
any way of finding what variations have been searched for?
No, you need to implement your own version of Porter stemming algorithm and
then mark up your results for hits.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Chris" <123@.123.com> wrote in message
news:eW4zscLPGHA.3924@.TK2MSFTNGP14.phx.gbl...
>I am using the Freetext predicate to search a column to search for phrases
>and variations on phrases.
> So say I just use a single phrase to search on e.g. 'enquiry' it will
> return variations such as 'enquire', 'enquiry', 'enquires' etc. This works
> fine. What I would like, is when I display the results to highlight the
> variation a little like Google groups.
> If you can imagine I have enquiry it a returned block of text it will be
> highlighted. This would be pretty straight forward if the only returned
> word was enquiry as I could find that. But what about the variations. Is
> there any way of finding what variations have been searched for?
>
|||Are there any tutorials or guides. I have no idea how to do this.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23mQ7UJNPGHA.1192@.TK2MSFTNGP11.phx.gbl...
> No, you need to implement your own version of Porter stemming algorithm
> and then mark up your results for hits.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Chris" <123@.123.com> wrote in message
> news:eW4zscLPGHA.3924@.TK2MSFTNGP14.phx.gbl...
>
|||You might want to look at some of the Lucene ports to the .Net platform to
see how they do this.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Chris" <123@.123.com> wrote in message
news:uV9BkMQPGHA.812@.TK2MSFTNGP10.phx.gbl...
> Are there any tutorials or guides. I have no idea how to do this.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23mQ7UJNPGHA.1192@.TK2MSFTNGP11.phx.gbl...
>

Filtering Single Recordset

Hello, I am a noob, so here goes. I would like to filter a resultset
returned from a stored procedure into 2 tables (e.g., summary section and
detail section). I would like to avoid 2 hits to the dB, so am returning a
summary and detailed resultset in 1 recordset. What I would like to do is
filter recordset 1 (say RectType = 1) into a summary table and filter
recordset 2 (say RectType = 2) into a detailed table. This has to be a
simple solution, but I must be missing something. Here's what I have tried
so far:
1) Adding filter on Details Grouping in the table - Nothing happens.
2) Adding filter in table Properties - Receive error: "An error has occurred
druing report processing. The processing of filter expression for table
'Summary' cannot be performed. The comparison failed. Please check the data
type returned by filter expression."
TIAI would think that you would need to do the filter on the table...but
that you are comparing two different data types. Try a convert to char
or a convert to int so that your data from the recordset and the data
in the filter are of the same type.|||OMG - I didn't even think of converting to char and that worked!!! Thanks a
bunch dude!
"Luke" wrote:
> I would think that you would need to do the filter on the table...but
> that you are comparing two different data types. Try a convert to char
> or a convert to int so that your data from the recordset and the data
> in the filter are of the same type.
>