Finding Missing Records

I have 18 tables that are all related by the primary key. When I join all
the fields together for reporting, it only shows the records that have all
the data filled in. How can I find which tables don't have a record?
Thanks,
DrewLEFT JOIN
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>I have 18 tables that are all related by the primary key. When I join all
>the fields together for reporting, it only shows the records that have all
>the data filled in. How can I find which tables don't have a record?
> Thanks,
> Drew
>|||Didn't think it would be that easy! Thought I would have to use an EXIST
query for this, and couldn't figure out how to incorporate all the tables
into 1 query...
Thanks,
Drew
"Michael C#" <howsa@.boutdat.com> wrote in message
news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
> LEFT JOIN
> "Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
> news:%23O17e%233OFHA.2144@.TK2MSFTNGP09.phx.gbl...
>|||SELECT *
FROM Table1
LEFT JOIN Table2
WHERE Table2.Field1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||OOPS, left out the ON clause:
SELECT *
FROM Table1
LEFT JOIN Table2
ON Table1.Column1 = Table2.Column1
WHERE Table2.Column1 IS NULL
"Drew" <drew.laing@.NOswvtc.dmhmrsas.virginia.SPMgov> wrote in message
news:uRZzCL4OFHA.3072@.TK2MSFTNGP09.phx.gbl...
> Didn't think it would be that easy! Thought I would have to use an EXIST
> query for this, and couldn't figure out how to incorporate all the tables
> into 1 query...
> Thanks,
> Drew
> "Michael C#" <howsa@.boutdat.com> wrote in message
> news:%23EbjnA4OFHA.2680@.TK2MSFTNGP09.phx.gbl...
>|||>> When I join all the fields together for reporting, it only shows the
Depending on your requirements, you can use OUTER JOINs or correlated
subqueries to retrieve all rows in one table which does not have a matching
value for a joining column in another table.
Anith

Finding mins and max time from records with different start time other that 12:00 midnight

I have a series of start and end time records. The problem is to select the min and max time from the series of records.

The following contraint applies. The start of broadcast time is 6:00 am. That is, minimum for start time is 6:00 am and maximum for end time is 5:59 am. So the following is illegal for start and end time, for example, 4:00 am - 6:30 am because it crosses the broadcast start time of 6:00 am.

Start End

10:00 pm -- 2:00 am

6:30 am -- 8:30 am

2:00 am - 3:45 am

11:00 am - 4:00pm

12:00 am - 3:40 am

You might be tempted to used -> Select MIN(Start), Max(End), but that will return 12:00 am - 4:00 pm, which is wrong, because sql server uses 12 midnight at the start time.

Can' t seem to come up with the tsql, please help

In my opinion it's not totally clear exactly what you're looking for.

In the example that you supplied, what results would you expect to be returned by the query? Also, are you storing the data as DATETIME values?

Thanks
Chris

|||Sorry. Since the min begin time is 6:00 am and the maximum end time time is 5:59 am, the expected result should be 6:30 am - 3:45am.|||

Thanks for providing the extra info.

Please could you also clarify what datatype you are using to store the times as this will affect the solution.

Thanks
Chris

|||

job:

I think that I also am not sure exactly what you are trying to do. Maybe a starting point is to substract six hours from your "startTime" to establish a "work day"; something like:

declare @.timeStuff table
( shiftId integer,
startTime datetime,
endTime datetime
)

insert into @.timeStuff values ( 1, '3/4/7 22:00', '3/5/7 2:00' )
insert into @.timeStuff values ( 2, '3/5/7 06:30', '3/5/7 8:30' )
insert into @.timeStuff values ( 3, '3/5/7 02:00', '3/5/7 3:45' )
insert into @.timeStuff values ( 4, '3/5/7 11:00', '3/5/7 16:00')
insert into @.timeStuff values ( 5, '3/6/7 00:00', '3/6/7 3:40')

declare @.offset datetime set @.offset = '06:00:00.000'

select convert (varchar(8), startTime - @.offset, 101) as [workDate ],
left(convert (varchar(8), startTime, 108), 5) as startTime,
left(convert (varchar(8), endTime, 108), 5) as endTime
from @.timestuff
order by startTime

-- workDate startTime endTime
-- -
-- 03/05/20 00:00 03:40
-- 03/04/20 02:00 03:45
-- 03/05/20 06:30 08:30
-- 03/05/20 11:00 16:00
-- 03/04/20 22:00 02:00

|||

If 4 am is not legal how did it get into your db in the first place, going by what you want there shoudl be nothing less than 6.00 am in your Start column. Get the place of entry sorted out and you should fine.

for the bad data u posted above you could still use

Select min(start), Max(End)

from tablename

where

convert(use conversion to convert time to last digits in time in start)>5.59 and

and convert(use conversion to convert time to last digits in time)<

--you can figure out the where condtion

point is it can be done

Finding first X records that when sumed up meet a criteria (Threshold)

Hi I'm tryin to do a tsql statement that returns the first X records that meet a certain criteria (Threshold):

Say the table has 2 columns: ID & Count

And 2 rows:

ID, Count

1, 10

2, 10

I'm looking to return rows and values until a threshold is reached, so

if threshold is 8, query should return:

ID 1 and Count 8

if theshold i s 15, query should return

ID 1, Count 10

ID 2, Count 5

Any help on this would be appreciated, Thanks!

Hey Zorca. First of all, I'll assume for now that you're running SQL 2000. If you're using SQL 2005, let me know and I'll give you a simpler solution using new SQL 2005 features.

To achieve this, you'd first need to write a query to get the running totals for your given table. You can do this as follows (note that I'm using the table name 'ztmp_table'):

create table dbo.ztmp_table (iid int, cnt int)
go

insert dbo.ztmp_table (iid, cnt) select 1,10
insert dbo.ztmp_table (iid, cnt) select 2,10
insert dbo.ztmp_table (iid, cnt) select 3,10
go

-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
order by t1.iid
go

Once you have that in place, you then use that query to fulfill the remainder of your mission. I'm going to simplify it by wrapping the above query in a view, removing the order by clause, as follows:

create view dbo.ztmp_runtotals as

-- Get a running total of the cnt column for the table
select t1.iid, min(t1.cnt) as mycnt, sum(t2.cnt) as runcnt
from ztmp_table t1
join ztmp_table t2
on t2.iid <= t1.iid
group by t1.iid
go

Now that I have that view to make simpler code, you can use the following script to see how you can achieve what you're looking for:

declare @.thresh int
set @.thresh = 15

select iid,
case
when runcnt > @.thresh then (@.thresh - (runcnt - mycnt))
when runcnt <= @.thresh then mycnt
end as cnt
from dbo.ztmp_runtotals as tmp1
where tmp1.iid <=
isnull((select min(iid) from dbo.ztmp_runtotals where runcnt >= @.thresh),iid)
order by tmp1.iid

I'm not going to spend a lot of time going into detail on how it works, I'll let you figure that part out, but feel free to repost any questions you may have. Play around with the @.thresh parameter a bit to see the different results you get.

HTH,

Finding duplicate values in fields

Hi everyone..

I have a table with about 40,000 records in it.
I want to find records where all of the values in all of the fields, except for the unique field are equal.

The records in this table are answers to a online survey.

So, I want to find all of the records where people answered the questions exactly the same as someone else.

Any help would be appreciated.

NickieWhat about this:

select list of fields
from yourtable
group by list of fields having count(*)>1

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 records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRD
Check the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database...m-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>
sql

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
[url]http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html[
/url]
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:

>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding Duplicate Records

CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>

Finding duplicate records

I want to clean out our database. We have alot of duplicate records. I want
to run a query on email address to find all the remove all but one of the
duplicate records. I am only working with one table. Can someone help?
TIA
TRDCheck the following articles - they will walk you through
the process:
How to remove duplicate rows from a table in SQL Server
http://support.microsoft.com/?id=139444
How do I remove duplicates from a table?
http://databases.aspfaq.com/database/how-do-i-remove-duplicates-from-a-table.html
-Sue
On Wed, 27 Sep 2006 17:02:28 -0400, "TRD"
<tdejohnx2@.hotmail.com> wrote:
>I want to clean out our database. We have alot of duplicate records. I want
>to run a query on email address to find all the remove all but one of the
>duplicate records. I am only working with one table. Can someone help?
>TIA
>TRD
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...
paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join (select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...
|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegro ups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected resul
t?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding displaying and deleteing dulpicate records

HI all,
I am trying to find duplicate records ina database that I have taken over. I
am using group bys and count() to find the sups.
I also realise that we could have a problem with misspellings and hterefore
not have a duplicate as in Joe Soape, Jo Soap, Joe Sope Joe Soap. These
couldpossible be sups of the same. I would terefore assume to allow for user
to decide what is dups and what to keep.
Are there any other more acceptable ways and means of doing this.
THis a a sample of what I use
SELECT Name, address1, address2,city, COUNT(*) as numbofdups FROM reinsurer
GROUP BY Name, address1, address2, city HAVING COUNT(*) > 1
Thanks
RobertIf you are using SQL 2005 developer / enterprise edition you could use the
Fuzzy Grouping / Term Extraction transformations in Integration Services and
work it out through that.
Aside from that you need to role your own in T-SQL, there are functions like
SOUNDEX that help to an extent but aren't that brilliant.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Robert Bravery" <me@.u.com> wrote in message
news:u0uuiXbbGHA.3352@.TK2MSFTNGP03.phx.gbl...
> HI all,
> I am trying to find duplicate records ina database that I have taken over.
> I
> am using group bys and count() to find the sups.
> I also realise that we could have a problem with misspellings and
> hterefore
> not have a duplicate as in Joe Soape, Jo Soap, Joe Sope Joe Soap. These
> couldpossible be sups of the same. I would terefore assume to allow for
> user
> to decide what is dups and what to keep.
> Are there any other more acceptable ways and means of doing this.
> THis a a sample of what I use
> SELECT Name, address1, address2,city, COUNT(*) as numbofdups FROM
> reinsurer
> GROUP BY Name, address1, address2, city HAVING COUNT(*) > 1
> Thanks
> Robert
>
>

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

Finding Changed Records

I need to create a table that would be the result set of a comparison
between table a and table b? Table a and b first 2 fields will always be
the same (CustomerName and CustomerNumber). But if the Address1 field
changes in table a, I would like to throw that whole row into my
comparison table. Almost like a Select Into with a sub query that would
include a WHERE TableA.field <> TableB.field. I would need to do this
comparison for about 8 fields. Help appreciated for my syntax is pretty
bad. Thanks.

Steve

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

Assuming that the first two fields are a primary key then you need to join
on the primary keys and difference the other columns:

e.g

INSERT INTO DIFFERENCES ( Col1, Col2 , Col3, Col4, Col5, COl6, Col7, Col8 )
SELECT A.Col1, A.Col2, A.Col3, A.Col4
FROM TableA A JOIN TableB B JOIN A.Col1 = B.Col1AND A.Col2 = B.Col2
WHERE A.COl3 <> B.Col3
OR A.Col4 <> B.Col4
OR A.Col5 <> B.Col5
OR A.Col6 <> B.Col6
OR A.Col7 <> B.Col7
OR A.Col8 <> B.Col8

John

"Steve Bishop" <steveb@.viper.com> wrote in message
news:4008ae65$0$70301$75868355@.news.frii.net...
> I need to create a table that would be the result set of a comparison
> between table a and table b? Table a and b first 2 fields will always be
> the same (CustomerName and CustomerNumber). But if the Address1 field
> changes in table a, I would like to throw that whole row into my
> comparison table. Almost like a Select Into with a sub query that would
> include a WHERE TableA.field <> TableB.field. I would need to do this
> comparison for about 8 fields. Help appreciated for my syntax is pretty
> bad. Thanks.
> Steve
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Finding and Grouping Records off of a given field value

In the "tblEmailGroupLink" table...

I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.

In the "tblEmailAddress" table...

The same "Emailid" field has a corresponding "EmailAddress" field.

What needed is all the email addresses found in the "EmailAdddress" field of these records.

I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.

Thanks,

Bill

Something like this:

Code Snippet

SELECT ea.EmailAddress
FROM tblEmailAddress ea
JOIN tblEmailGroupLink eg
ON ea.EmailID = eg.EmailID
WHERE eg.UnSubscribed = 'True'

|||

Arnie,

Thank you very much, it works perfect.

Bill

Finding and Deleting Duplicate Records

I have a table that has many duplicate records and I need to delete all but
one of the duplicate records. Is there a way I can identify these records
with a sql statement?
Example record:
(Comment_id, site_id, date_entered, user_id, comment)
6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
Mike,
Depending on the size of the comment field you could do something like this:
SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
FROM <TABLENAME>
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
|||Thanks, but how would I delete all the other records that I don't want?
"Jerry Spivey" wrote:

> Mike,
> Depending on the size of the comment field you could do something like this:
> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> FROM <TABLENAME>
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>
>
|||Mike,
Can you post the DDL for the table(s) so I don't have to guess about the
keys?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...[vbcol=seagreen]
> Thanks, but how would I delete all the other records that I don't want?
> "Jerry Spivey" wrote:
|||CREATE TABLE [dbo].[StatusRptComments] (
[Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
[Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
(
[Comment_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
"Jerry Spivey" wrote:

> Mike,
> Can you post the DDL for the table(s) so I don't have to guess about the
> keys?
> Thanks
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>
>
|||Mike,
What criteria do you want for the DELETE? I.e., ok the comment column is
the same for x records but the other columns may be different i.e,
comment_id, dateentered, etc... Which one record do you want to retain?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...[vbcol=seagreen]
> CREATE TABLE [dbo].[StatusRptComments] (
> [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> (
> [Comment_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> "Jerry Spivey" wrote:
|||Sorry...I want to keep the entry with the latest date. Thanks for your
patience with my not providing complete details up front.
"Jerry Spivey" wrote:

> Mike,
> What criteria do you want for the DELETE? I.e., ok the comment column is
> the same for x records but the other columns may be different i.e,
> comment_id, dateentered, etc... Which one record do you want to retain?
> Thanks
> Jerry
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
>
>
|||Mike,
Try:
BEGIN TRAN
DELETE #STATUSRPTCOMMENTS
FROM #STATUSRPTCOMMENTS S2
WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
WHERE S1.SITE_NO = S2.SITE_NO)
--ROLLBACK TRAN
--OR
--COMMIT TRAN
My mind is shot today so use this explicit tran and see if it works if not
roll back and tell me what is wrong and I'll look at it this weekend.
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...[vbcol=seagreen]
> Sorry...I want to keep the entry with the latest date. Thanks for your
> patience with my not providing complete details up front.
> "Jerry Spivey" wrote:
|||That works great...thanks Jerry.
"Jerry Spivey" wrote:

> Mike,
> Try:
> BEGIN TRAN
> DELETE #STATUSRPTCOMMENTS
> FROM #STATUSRPTCOMMENTS S2
> WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
> AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
> WHERE S1.SITE_NO = S2.SITE_NO)
> --ROLLBACK TRAN
> --OR
> --COMMIT TRAN
> My mind is shot today so use this explicit tran and see if it works if not
> roll back and tell me what is wrong and I'll look at it this weekend.
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
>
>

Finding and Deleting Duplicate Records

I have a table that has many duplicate records and I need to delete all but
one of the duplicate records. Is there a way I can identify these records
with a sql statement?
Example record:
(Comment_id, site_id, date_entered, user_id, comment)
6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
non-franchise signage.
8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
non-franchise signage.Another thing to consider. The comment field is a text data type.
"Mike Collins" wrote:
> I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Mike,
Depending on the size of the comment field you could do something like this:
SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
FROM <TABLENAME>
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>I have a table that has many duplicate records and I need to delete all but
> one of the duplicate records. Is there a way I can identify these records
> with a sql statement?
> Example record:
> (Comment_id, site_id, date_entered, user_id, comment)
> 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.
> 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> non-franchise signage.|||Thanks, but how would I delete all the other records that I don't want?
"Jerry Spivey" wrote:
> Mike,
> Depending on the size of the comment field you could do something like this:
> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> FROM <TABLENAME>
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >I have a table that has many duplicate records and I need to delete all but
> > one of the duplicate records. Is there a way I can identify these records
> > with a sql statement?
> >
> > Example record:
> > (Comment_id, site_id, date_entered, user_id, comment)
> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
> >
> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> > non-franchise signage.
>
>|||Mike,
Can you post the DDL for the table(s) so I don't have to guess about the
keys?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> Thanks, but how would I delete all the other records that I don't want?
> "Jerry Spivey" wrote:
>> Mike,
>> Depending on the size of the comment field you could do something like
>> this:
>> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> FROM <TABLENAME>
>> HTH
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >I have a table that has many duplicate records and I need to delete all
>> >but
>> > one of the duplicate records. Is there a way I can identify these
>> > records
>> > with a sql statement?
>> >
>> > Example record:
>> > (Comment_id, site_id, date_entered, user_id, comment)
>> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>> >
>> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> > non-franchise signage.
>>|||CREATE TABLE [dbo].[StatusRptComments] (
[Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
[Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
(
[Comment_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
"Jerry Spivey" wrote:
> Mike,
> Can you post the DDL for the table(s) so I don't have to guess about the
> keys?
> Thanks
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> > Thanks, but how would I delete all the other records that I don't want?
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Depending on the size of the comment field you could do something like
> >> this:
> >>
> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> FROM <TABLENAME>
> >>
> >> HTH
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >I have a table that has many duplicate records and I need to delete all
> >> >but
> >> > one of the duplicate records. Is there a way I can identify these
> >> > records
> >> > with a sql statement?
> >> >
> >> > Example record:
> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >> >
> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> > non-franchise signage.
> >>
> >>
> >>
>
>|||Mike,
What criteria do you want for the DELETE? I.e., ok the comment column is
the same for x records but the other columns may be different i.e,
comment_id, dateentered, etc... Which one record do you want to retain?
Thanks
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> CREATE TABLE [dbo].[StatusRptComments] (
> [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> (
> [Comment_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> "Jerry Spivey" wrote:
>> Mike,
>> Can you post the DDL for the table(s) so I don't have to guess about the
>> keys?
>> Thanks
>> Jerry
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> > Thanks, but how would I delete all the other records that I don't want?
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Depending on the size of the comment field you could do something like
>> >> this:
>> >>
>> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> FROM <TABLENAME>
>> >>
>> >> HTH
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >I have a table that has many duplicate records and I need to delete
>> >> >all
>> >> >but
>> >> > one of the duplicate records. Is there a way I can identify these
>> >> > records
>> >> > with a sql statement?
>> >> >
>> >> > Example record:
>> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >> >
>> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> > non-franchise signage.
>> >>
>> >>
>> >>
>>|||Sorry...I want to keep the entry with the latest date. Thanks for your
patience with my not providing complete details up front.
"Jerry Spivey" wrote:
> Mike,
> What criteria do you want for the DELETE? I.e., ok the comment column is
> the same for x records but the other columns may be different i.e,
> comment_id, dateentered, etc... Which one record do you want to retain?
> Thanks
> Jerry
>
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> > CREATE TABLE [dbo].[StatusRptComments] (
> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> > NULL ,
> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> > (
> > [Comment_id]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> Can you post the DDL for the table(s) so I don't have to guess about the
> >> keys?
> >>
> >> Thanks
> >>
> >> Jerry
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> > Thanks, but how would I delete all the other records that I don't want?
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Depending on the size of the comment field you could do something like
> >> >> this:
> >> >>
> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> FROM <TABLENAME>
> >> >>
> >> >> HTH
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >I have a table that has many duplicate records and I need to delete
> >> >> >all
> >> >> >but
> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> > records
> >> >> > with a sql statement?
> >> >> >
> >> >> > Example record:
> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >> >
> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> > non-franchise signage.
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||Mike,
Try:
BEGIN TRAN
DELETE #STATUSRPTCOMMENTS
FROM #STATUSRPTCOMMENTS S2
WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
WHERE S1.SITE_NO = S2.SITE_NO)
--ROLLBACK TRAN
--OR
--COMMIT TRAN
My mind is shot today so use this explicit tran and see if it works if not
roll back and tell me what is wrong and I'll look at it this weekend.
HTH
Jerry
"Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> Sorry...I want to keep the entry with the latest date. Thanks for your
> patience with my not providing complete details up front.
> "Jerry Spivey" wrote:
>> Mike,
>> What criteria do you want for the DELETE? I.e., ok the comment column is
>> the same for x records but the other columns may be different i.e,
>> comment_id, dateentered, etc... Which one record do you want to retain?
>> Thanks
>> Jerry
>>
>> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
>> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
>> > CREATE TABLE [dbo].[StatusRptComments] (
>> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
>> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ,
>> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
>> > NULL ,
>> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
>> > ) ON [PRIMARY]
>> > GO
>> >
>> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
>> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
>> > (
>> > [Comment_id]
>> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
>> > GO
>> >
>> > "Jerry Spivey" wrote:
>> >
>> >> Mike,
>> >>
>> >> Can you post the DDL for the table(s) so I don't have to guess about
>> >> the
>> >> keys?
>> >>
>> >> Thanks
>> >>
>> >> Jerry
>> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> message
>> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
>> >> > Thanks, but how would I delete all the other records that I don't
>> >> > want?
>> >> >
>> >> > "Jerry Spivey" wrote:
>> >> >
>> >> >> Mike,
>> >> >>
>> >> >> Depending on the size of the comment field you could do something
>> >> >> like
>> >> >> this:
>> >> >>
>> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
>> >> >> FROM <TABLENAME>
>> >> >>
>> >> >> HTH
>> >> >>
>> >> >> Jerry
>> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
>> >> >> message
>> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
>> >> >> >I have a table that has many duplicate records and I need to
>> >> >> >delete
>> >> >> >all
>> >> >> >but
>> >> >> > one of the duplicate records. Is there a way I can identify these
>> >> >> > records
>> >> >> > with a sql statement?
>> >> >> >
>> >> >> > Example record:
>> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
>> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >> >
>> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
>> >> >> > non-franchise signage.
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||That works great...thanks Jerry.
"Jerry Spivey" wrote:
> Mike,
> Try:
> BEGIN TRAN
> DELETE #STATUSRPTCOMMENTS
> FROM #STATUSRPTCOMMENTS S2
> WHERE SITE_NO = S2.SITE_NO AND ADMINID = S2.ADMINID AND COMMENT = S2.COMMENT
> AND S2.DATEENTERED <> (SELECT MAX(DATEENTERED) FROM #STATUSRPTCOMMENTS S1
> WHERE S1.SITE_NO = S2.SITE_NO)
> --ROLLBACK TRAN
> --OR
> --COMMIT TRAN
> My mind is shot today so use this explicit tran and see if it works if not
> roll back and tell me what is wrong and I'll look at it this weekend.
> HTH
> Jerry
> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> news:6476D0E1-1054-429E-A456-35982AA76A29@.microsoft.com...
> > Sorry...I want to keep the entry with the latest date. Thanks for your
> > patience with my not providing complete details up front.
> >
> > "Jerry Spivey" wrote:
> >
> >> Mike,
> >>
> >> What criteria do you want for the DELETE? I.e., ok the comment column is
> >> the same for x records but the other columns may be different i.e,
> >> comment_id, dateentered, etc... Which one record do you want to retain?
> >>
> >> Thanks
> >>
> >> Jerry
> >>
> >>
> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in message
> >> news:5CB48CB2-7776-4C0A-BA97-D0ADDE8171C7@.microsoft.com...
> >> > CREATE TABLE [dbo].[StatusRptComments] (
> >> > [Comment_id] [int] IDENTITY (1, 1) NOT NULL ,
> >> > [Site_no] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [DateEntered] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ,
> >> > [AdminID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> >> > [Suellen_Comment] [varchar] (2500) COLLATE SQL_Latin1_General_CP1_CI_AS
> >> > NULL ,
> >> > [comment_type] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> >> > ) ON [PRIMARY]
> >> > GO
> >> >
> >> > ALTER TABLE [dbo].[StatusRptComments] WITH NOCHECK ADD
> >> > CONSTRAINT [PK_StatusRptComments] PRIMARY KEY CLUSTERED
> >> > (
> >> > [Comment_id]
> >> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> >> > GO
> >> >
> >> > "Jerry Spivey" wrote:
> >> >
> >> >> Mike,
> >> >>
> >> >> Can you post the DDL for the table(s) so I don't have to guess about
> >> >> the
> >> >> keys?
> >> >>
> >> >> Thanks
> >> >>
> >> >> Jerry
> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> message
> >> >> news:62C48B0E-9B92-4036-9A23-3244889684A9@.microsoft.com...
> >> >> > Thanks, but how would I delete all the other records that I don't
> >> >> > want?
> >> >> >
> >> >> > "Jerry Spivey" wrote:
> >> >> >
> >> >> >> Mike,
> >> >> >>
> >> >> >> Depending on the size of the comment field you could do something
> >> >> >> like
> >> >> >> this:
> >> >> >>
> >> >> >> SELECT DISTINCT(CONVERT(VARCHAR(8000),COMMENT))
> >> >> >> FROM <TABLENAME>
> >> >> >>
> >> >> >> HTH
> >> >> >>
> >> >> >> Jerry
> >> >> >> "Mike Collins" <MikeCollins@.discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:BB36D700-DB54-43BC-8734-70634BA8B7ED@.microsoft.com...
> >> >> >> >I have a table that has many duplicate records and I need to
> >> >> >> >delete
> >> >> >> >all
> >> >> >> >but
> >> >> >> > one of the duplicate records. Is there a way I can identify these
> >> >> >> > records
> >> >> >> > with a sql statement?
> >> >> >> >
> >> >> >> > Example record:
> >> >> >> > (Comment_id, site_id, date_entered, user_id, comment)
> >> >> >> > 6978, AL28492, 07/17/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >> >
> >> >> >> > 8161, AL28492, 08/15/2005, 408, No longer a Franchise, replace w/
> >> >> >> > non-franchise signage.
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>