Remove Duplicates Using Trigger?

Remove Duplicates
I have a query which gives the following output, How can i get a output like this:

QUERY

COL1COL2COL3
A1AAGG
A1BBHH
A1CCJJ
B1DDKK
B1EELL
B1FFMM

OUTPUT

COL1COL2COL3
A1AAGG
BBHH
CCJJ
B1DDKK
EELL
FFMM

View Replies !

---

Remove Neighbouring Duplicates
Welcome,how can I alter following table in order to reduce neighbouringduplicates (symbol, position, quantity, price).Nr Symbol Position QuantityPrice Date1. wz9999b 1 1.02500.0 2007-05-09 08:09:42.6532. wz9999b 2 12.02500.0 2007-05-09 08:09:42.6533. wz9999b 1 100.02590.0 2007-05-10 15:47:04.1404. PZ0008VX 1 2280.8842090.55000000000022007-05-1612:43:12.4035. PZ0008VX 1 2280.8842102.05000000000022007-05-1612:45:27.4206. wz9999b 1 0.0012500.0 2007-05-18 09:47:16.0337. wz9999b 1 0.0012500.0 2007-05-18 09:47:53.2708. wz9999b 1 1.01.0 2007-05-22 12:35:07.8939. PZ0008VX 1 2280.8842102.05000000000022007-05-2409:38:26.16010. PZ0008VX 1 2280.8842102.05000000000022007-05-2409:38:38.80011. wz9999b 1 0.001 2500.02007-05-24 12:35:07.20712 wz9999b 1 0.002 2500.02007-05-24 12:35:14.98713. wz9999b 1 0.001 2500.02007-05-24 12:38:07.207In the result set I would like to get the rows number 6 and 10.Any suggestions??

View Replies !

---

Remove Duplicates Within Pipeline
I have a situation where we get XML files sent daily that need uploading into SQL Server tables, but the source system producing these files sometimes generates duplicate records in the file.  The tricky part is, that the record isn't entirely duplicated.  What I mean, is that if I look for duplicates by grouping the key columns, having count(*) > 1, I find which ones are duplicates, but when I inspect the data on these duplicates, the other details in the remaining columns may differ.  So our rule is: pick the first record, toss the rest of the duplicates.

Because we don't sort on any columns during the import, the first record kept of the duplicates is arbitrary.  Again, we can't tell at this point which of the duplicated records is more correct.  Someday down the road, we will do this research.

Now, I need to know the most efficient way to accomplish this in SSIS.  If it makes it easier, I could just discard all the duplicates, since the number of them is so small.

If the source were a relational table, I could use a SQL statement to filter the records to remove the duplicates, but since the source is an XML file, I don't know how to filter these out in the pipeline, since the file has to be aggregated to search for dups.

Thanks

Kory

View Replies !

---

Remove Duplicates On A Table
Hi All
I have the dbo.OperatingHour It has many duplicates and I want to remove duplicates permanently
The statement below works but when I open the table there are no changes

Insert into OperatingHour(Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays])
(SELECT DISTINCT Weekdays, Wednesdays, Fridays,Saturdays, [Sundays/Public Holidays] FROM OperatingHour)

View Replies !

---

Can Someone Proofread My 'remove Duplicates' Script?
DELETE
FROM tblContacts
WHERE tblContacts.ID IN(
SELECT F.ID
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
)
AND tblContacts.ID NOT IN(
SELECT Min(ID)
FROM tblContacts AS F
WHERE Exists (
SELECT email, Count(ID)
FROM tblContacts
WHERE tblContacts.email = F.email
GROUP BY tblContacts.email
HAVING Count(tblContacts.ID) > 1
)
GROUP BY email
)



I readily admit that I've shamelessly copied 'n pasted this from a tutorial and then taken a stab at tweaking it for my own ends. But I really don't understand what it's doing.

Really, all I want to know is that it will remove records with duplicate email fields. But I could also do with confirming - looking at the "SELECT Min(ID)" bit - does that mean that if it finds a duplicate, it'll delete the latest-added one? And if so, that changing it to remove the earliest-added one is simply a case of changing MIN to MAX?


Thanks :)

View Replies !

---

Is There A Way To Remove Table Rows Duplicates
If we want to remove the duplicate row and leave only one row instead of 2 or 3 rows for example with the same column values.

2/ The same question but when all the columns of the row are duplicate except the id field.
Thanks a lot.

View Replies !

---

Custom Property For Remove Duplicates Transform Input Row
Im working through the MS example of "removeDuplicates". I cant seem to figure out how to add custom property for input column.

I added the helper method:
        private static void AddIsKeyCustomPropertyToInput(IDTSInput90 input, object value)
        {
            IDTSCustomProperty90 isKey = input.CustomPropertyCollection.New();
            isKey.Name = "IsKey";
            isKey.Value = value;
        }
I call it from:
public override void ProvideComponentProperties()
{
//...
AddIsKeyCustomPropertyToInput(input, false);
//...
}
public override void ReinitializeMetaData()
        {
            IDTSInput90 input = ComponentMetaData.InputCollection[0];
            if (input.CustomPropertyCollection.Count == 0)
            {
                AddIsKeyCustomPropertyToInput(input, false);
            }
// ...
}

However when I deployed it and added the component to SSIS package - I cant see the Custom Column "IsKey" in the input column properties window.
What am I missing - please help

View Replies !

---

Duplicates Again! UNION Join - Remove Records With Column Diff.
Hello All,

We all were new at one point.... any help is appreciated.

Objective:

Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)

Reason:

I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.

My progress/where I'm stuck:

Here is my first query/union:

SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];

As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....

an example of duplicates that I must remove are as follows:

142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)

I am trying to run another query on my first query results so I don't mess my first query up. Here it is:

SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";


Please Help! Thank you in advance.

--------------------

5 minutes away from pulling my last one!

BaldNAskewed

View Replies !

---

Sort Component, Remove Duplicates, Comparison Flags - Ignore Case
What happens when you add the Ignore Case flag into the mix?
 
I'm having a hell of a time - I'm dealing with an SCD situation using TableDifference component and I have both existing dimensions and new data coming in, each go through identical Case-Insensitive/Sort with remove duplicates, but I'm getting identical new and deleted records detected - I think because of ordering issues.  I'm still trying to whittle the test case down, but I think data from all around the records I'm investigating seems to get sorted in between them, so I'm having trouble getting a small test case built.
 
I think the mixed case data is the root of the problem, and I think the design is bad, but before I go back to the technical lead, I need to understand enough to show that you cannot take two pipelines sorted and de-duped case-insensitively and then do a case-sensitive table difference operation.

View Replies !

---

Trigger To Prevent Duplicates
Hi all,

I'm writing a trigger to prevent duplicates. I know that this can be done through primary key or unique constraints but in the real world my uniqueness is defined by 8 columns which is too a big an index to maintain on the primary / unique key.

If I create a table with 2 columns

CREATE TABLE Table1
(CentreCHAR(10),
Month CHAR(3)
)


Then create a trigger to prevent duplicates

CREATE TRIGGER trigger_Check_Duplicates
ON Table1
FOR INSERT, UPDATE
AS
-- This trigger has been created to check that duplicate rows are not inserted into AudioVisual table.
DECLARE @IsDuplicate INTEGER
-- Check if row exists
SELECT @IsDuplicate = 1
FROM Inserted i, Table1 t
WHERE t.Centre = i.Centre
AND t.Month = i.Month
IF (@IsDuplicate = 1)
-- Display Error and then Rollback transaction
BEGIN
RAISERROR ('This row already exists in the table', 16, 1)
ROLLBACK TRANSACTION
END

Then insert a row into the new table (no other data is in there)

INSERT Table1
VALUES('0691040176','AUG')

I get the Trigger error message that the row already exists. Why is this the case? I though that Table 1 (target table) would show no entries as it has no data - it should be a before image of the table and the inserted table should be an after image.


Please help!!!


Thanks

Neill

View Replies !

---

Create TRIGGER Remove White Spaces From A Fields In Table-scan And Fix
hi
i have table i use it for update insert
and the users use this table from a grid on the web
and i need to prevent from white space in the fields in table
so how to
create TRIGGER remove white space from a fields in table scan and fix it ?

 



Code Snippet
SELECT  TRIM(fieldname)
,            LTRIM(fieldname)
,            RTRIM(fieldname)
,            LTRIM(RTRIM(fieldname))
FROM     tablename
 
 



Code Snippet
WHERE     (LTRIM(RTRIM(fieldname)) = 'Approve')
 



Code Snippet
replace(@text,' ','')
 
 





create TRIGGER on update insert  and not to  damage the text in the all fields
TNX

View Replies !

---

How Do I Clean Up The SQL Server (ctp) From ADD/REMOVE Program Without The Change/remove Button
I have uninstalled the CTP version of the SQL Server express so that I can install the released version but CTP version is still listed in the add/remove program list but without the change/remove button. I have been to different sites to find information on cleaning this up and I have ran all the uninstall tool I can find but the problem still prevails. I cannot install the released version without completely getting rid of the CTP version. Please help anyone.

Thanks

deebeez1

View Replies !

---

Unable To Remove SQL Instance In Add/Remove Program
I need help,

I am having a hard time removing my SQL instance inside the Add/Remove program.  After i select the SQL Instance name and then I tried to remove it but it won't allow me to delete it.  There isn't any error message or whatsoever.  Actually, when i try to log it in my SQL Management studio, that certain sql instance name is not existing according to the message box.  Is there any way to remove the Sql Instance in my system?

I appreciate your help, Thanks

IS Support

View Replies !

---

SQL Server Management Studio: Can Not Remove AdventureWorksDB In &&"Add Or Remove Programs&&" Of Control Panel
Hi all,

I tried to remove AdventureWorksDB in the "Add or Remove Programs" of Contol Panel and I got the following errors: (1) AdventureWorksDB     Error 1326: Error getting file security: CProgram FilesMicrosoft SQL ServerMSSQL1MSSQLGetLastError: 5.      |OK|   and (2) Add or Remove Programs   Fatal Error during installation (after I clicked the |OK| button).   Please help and tell me how I can solve this problem.

Thanks in advance,

Scott  Chang 

View Replies !

---

SQL Duplicates
I need some help.
I have created a database that looks like the following:
FirstName Table link to Main Table.
I have created a Stored procedure that looks like this:
Create procedure dbo.StoredProcedure
(
   @FirstName   varchar(20)
)
Declare FirstNameID int
Insert Into Main Table
(
FirstName
)
Values
(
@FirstName
)
Select @FirstNameID = Scope_Identity()
How could I redesign this to check if a value exists and if it exists then simply use that value instead of creating a new duplicate value?
 
Thanks!!

View Replies !

---

Msg 512 But No Duplicates!?!
I am attempting to execute the Stored Procedure at the foot of thismessage. The Stored Procedure runs correctly about 1550 times, butreceive the following error three times:Server: Msg 512, Level 16, State 1, Procedure BackFillNetworkHours,Line 68Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I've done some digging, and the error message is moderatelyself-explanatory.The problem is that there is no Line 68 in the Stored Procedure. It'sthe comment line:-- Need to find out how many hours the employee is scheduled etc.Also, there are no duplicate records in the Employee table nor theWeeklyProfile table. At least I assume so - if the following SQL todetect duplicates is correct!SELECT E.*FROMEmployee Ejoin(select EmployeeIDfromEmployeeGroup by EmployeeIDhaving count(*) > 1) as E2On(E.EmployeeID = E2.EmployeeID)SELECTW.*FROMWeekProfile Wjoin(SelectWeekProfileIDFROMWeekProfileGROUP BYEmployeeID, MondayHours, WeekProfileIDHAVING COUNT(*) > 1) AS W2ONW.WeekProfileID = W2.WeekProfileIDNOTE: In the second statement, I have tried for MondayHours thruFridayHours.Anyone got any ideas? The TableDefs are set up in this thread:<http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/fff4ef21e9964ab8/f5ce136923ebffc3?q=teddysnips&rnum=1&hl=en#f5ce136923ebffc3>The Stored Procedure that causes the error is here:--************************************************** ***********CREATE PROCEDURE BackFillNetworkHoursASDECLARE @EmployeeID intDECLARE @TimesheetDate DateTimeDECLARE @NumMinutes intDECLARE @NetworkCode int-- Get the WorkID corresponding to Project Code 2002SELECT@NetworkCode = WorkIDFROM[Work]WHERE(WorkCode = '2002')-- Open a cursor on a SELECT for all Network Support Employees whereany single workday comprises fewer than 7.5 hoursDECLARE TooFewHours CURSOR FORSELECTEmployeeID,CONVERT(CHAR(8), Start, 112) AS TimesheetDate,SUM(NumMins) AS TotalMinsFROM(SELECTTI.EmployeeID,W.WorkCode,TI.Start AS Start,SUM(TI.DurationMins) AS NumMinsFROMTimesheetItem TI LEFT JOIN[Work] W ON TI.WorkID = W.WorkIDWHERE EXISTS(SELECT*FROMEmployee EWHERE((TI.EmployeeID = E.EmployeeID) AND(E.DepartmentID = 2)))GROUP BY TI.EmployeeID, TI.Start, W.WorkCode) AS xGROUP BYEmployeeID,CONVERT(char(8), Start, 112)HAVINGSUM(NumMins) < 450ORDER BYEmployeeID,CONVERT(CHAR(8), Start, 112)-- Get the EmployeeID, Date and Number of Minutes from the cursorOPEN TooFewHoursFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesWHILE (@@FETCH_STATUS=0)BEGINDECLARE @NewWorkTime datetimeDECLARE @TimesheetString varchar(50)DECLARE @Duration intDECLARE @RequiredDuration int-- Set the correct date to 08:30 - by default the cast from thecursor's select statement is middaySET @TimesheetString = @TimesheetDate + ' 08:30'SET @NewWorkTime = CAST(@TimesheetString AS Datetime)-- Need to find out how many hours the employee is scheduled to workthat day.SET @RequiredDuration = CASE (DATEPART(dw, @NewWorkTime))WHEN 1 THEN(SELECT CAST((60 * SundayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 2 THEN(SELECT CAST((60 * MondayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 3 THEN(SELECT CAST((60 * TuesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 4 THEN(SELECT CAST((60 * WednesdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 5 THEN(SELECT CAST((60 * ThursdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 6 THEN(SELECT CAST((60 * FridayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))WHEN 7 THEN(SELECT CAST((60 * SaturdayHours) AS int) FROM WeekProfile WHERE(EmployeeID = @EmployeeID))ENDIF @NumMinutes < @RequiredDurationBEGIN-- Set the Start for the dummy work block to 08:30 + the number ofminutes the employee has already worked that daySET @NewWorkTime = DateAdd(minute, @NumMinutes, @NewWorkTime)-- Set the duration for the dummy work block to be required durationless the amount they've already workedSET @Duration = @RequiredDuration - @NumMinutes-- Now we have the correct data - insert into table.INSERT INTO TimesheetItem(EmployeeID,Start,DurationMins,WorkID)VALUES(@EmployeeID,@NewWorkTime,@Duration,@NetworkCode)ENDFETCH NEXT FROM TooFewHours INTO @EmployeeID, @TimesheetDate,@NumMinutesENDCLOSE TooFewHoursDEALLOCATE TooFewHoursGO--************************************************** ***********ThanksEdward

View Replies !

---

Getting Rid Of Duplicates
I have a table, TEST_TABLE, with 6 columns (COL1, COL2, COL3, COL4,COL5, COL6).... I need to be able to select all columns/rows whereCOL3, COL4, and COL5 are unique....I have tried using DISTINCT and GROUP BY, but both will only allow meto access columns COL3, COL4, and COL5..... i need access to allcolumns...I just want to get rid of duplicate rows (duplicates ofCOL3, COL4, and COL5)...Thanks in advance.Joe

View Replies !

---

Duplicates
i've imported a number of spreadsheets into a database
the schema is

directoryid firstname, lastname, extension
703168 Andrew Lim 78094
703154 Joseph Egan 78888
704548 Andrew Lim 78094

I realized the spreadsheet had a large number of duplicates. How can i delete the duplicates ?
like deleting directoryid 703168 only.

View Replies !

---

Duplicates
i'm trying to get duplicates out of the my database

SELECT COUNT(*) AS Amount,
Firstname,
surname,
Internalextension
FROM iac.dbo.sf_profil
GROUP BY FirstName, surname, internalextension
HAVING COUNT(*) > 1 order by firstname, surname


How do i alter the query just retrieve records which have firstname and lastname which are similar but different extension numbers ?

View Replies !

---

Duplicates
I am having an issue trying to flag duplicate rows on a single table. For example, if there are two rows on the table which are identical, I want to update only one of the rows to mark it as a duplicate but I cannot find a way around it. I am using SQL 6.5
If you have any suggestions they would be appreciated.

View Replies !

---

Duplicates
have two tables. Table A contains client_no and accountno and table B contains the clientno,accountno,name,address,etc
l'm trying to select the data from table B that has both the clientno and accountno from table a matching the clientno and accountno in table b. Table a has 20 records
but when l do my selection l get more than 20records? l want to pupulate table a with the missing data that matches the two fields? Here is the query that l'm running. It returns duplicates when l run it.It should give me back the same number fo records as Table A

Table a already has a client and account number just need to populate the rest of the data into the table
insert into test(,,,,,,,etc)

SELECT ltrim(rtrim(left(a.Title,5))) As Title,
ltrim(rtrim(left(a.Surname,28))) As Surname,
ltrim(rtrim(left(a.First_Name1,28))) As First_Name1,

ltrim(rtrim(left(a.First_Name2,28))) First_Name2,
ltrim(rtrim(left(a.Address1,28))) As Address1,
ltrim(rtrim(left(a.Address2,28))) As Address2,
ltrim(rtrim(left(a.Address3,28)))+ ' ' + ltrim(rtrim(left(Address4,28))) As Address4,
ltrim(rtrim(left(a.PCode,4))) As PCode,

convert(numeric(8),ltrim(rtrim(left(a.Order_Date_G ranted,8)))) AS Date_Of_Action,
ltrim(rtrim(left(a.Court_Venue,8))) AS Court_Name,
ltrim(rtrim(left(a.Admin_Order_Type,2))) AS AO_Type,
ltrim(rtrim(left(a.Administrator_Name,35))) AS Administrator_Name,
ltrim(rtrim(left(a.Administrator_Tel,22))) As Administrator_Tel,
convert(numeric(8),ltrim(rtrim(left(a.Outstanding_ Bal,8)))) AS Amount

FROM TableA a, TableB b
where a.clientno = b.clientno and a.accountno = b.accountno

View Replies !

---

Getting Rid Of Duplicates
I have a dilema..... I have a databas eof about 60,000 users and i need to get rid of those users where there is a duplicate email address. I have written an asp utilty that works but is far too taxing on our little server and i thinkk itwill kill it. what it does is for each email address it compares it against all the others.... so for each address it checks against 60,000 other records 60,000 times.... you know what i mean. its pretty phucked.... i tested it on just one record and took about 5mins.

anyway ive been trying to do it in SQL with no luck

here is a simplified version of my dilema


*****
NAMEEMAILAGE
tomtom@mail23
tombomb tom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
marycool@mail89
thomas tom@mail13
richrich@mail65
richdick@mail65
tomtom@mail23
tomtom@mail23

so what i want to do is cut it down so there are no duplicate email addresses. I want the table looking like this:

*****
NAMEEMAILAGE
tomtom@mail23
petepete@email23
davecool@mail21
stevesteve@mail17
richrich@mail65
richdick@mail65

Can you think of a way to do this?

i tried the following but it gets rid of duplicates of other fields which is not what i want to do

CREATE table distinct_records
SELECT name, Email, Age
FROM duplicate_records
GROUP BY name, Email, Age;

any ideas guys?

thanks
tom.harrow@netpoll.net

View Replies !

---

Duplicates Only
I have the following data.

CustID RoleID RelatID StartDate EndDate

20 RIX AGQ 5/05/2007 31/12/9999

20 RIX AGR 10/06/2007 31/12/9999

18 LRS AGQ 3/09/2004 31/12/9999

22 SRT AGP 5/03/2007 31/12/9999

22 SRT AGP 10/03/2007 31/12/9999

 
I wish to SELECT only the records that have duplicates. So, in the above data example, RoleID of LRS should be excluded.
 
How to do?

View Replies !

---

Duplicates
Hi!
Grateful for some help as a newbie...
I have a
OLE db SQL command: SELECT DISTINCT convert (char(8),date,112) as day,...etc

Resulting in error "Violation of PRIMARY KEY constraint 'PK_Dim_Date"... so Column Day in Dim_date contains duplicates.

I suppose i need a delete in Lookup or how would I eliminate duplicates in Dim?
DELETE day from dim_date where day in(Select day from date ...

View Replies !

---

Sum Of Duplicates
Anyone know how to sum duplicated values so it only sums the distinct values?

View Replies !

---

Duplicates
Hi,
I'll see if I can explain this clearly. The query below
selects rows from the "hdr_ctl_nbr_status" table if the value in the field "tcn" from that table is found in the table "temp_tcn". I want all fields from the "hdr_ctl_nbr_status" table to be selected BUT only one row. In other words for a tcn with a value "12345678" there are 10 rows returned from the hdr_ctl_nbr_status table, I want only 1. Is there a way I can use SELECT DISTINCT to do this ? I know this usually functions on one or more fields but I want the DISTINCT to be on tcn only BUT return all fields in the query.

Select h.*,'' from hdr_ctl_nbr_status as h WITH (NOLOCK)
where h.tcn in (select tcn from temp_tcn)

Thanks, sorry if this is too confusing. Jeff

View Replies !

---

Duplicates
Hi,
This is the query which shows me the duplicates
Some of the records have more than one records
I would like to know how to delete the extra records so that I will end up with one record per row.

select
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last,
Count(*) as 'count'
from
tblPricesClean
group by
Pricing_Source,
VaR_Identifier,
Price_Date,
PX_Last
having count(*) > 1
order by
count desc

View Replies !

---

No Duplicates Please
Hi, All,

I have two columns of int data in the a table, as my example data shows below.

I want my data returned to be something like those in #test3, but my question is this, how can I do it without using #test2 and #test3?

By the way, the business requirement doesn't care it's min/max or any ID when one side has duplicated values.

Thanks!


Use tempdb
Go

if object_ID ('#test') is not null drop table #test

create table #test (col1 int, col2 int)
insert into #test
Select 123, 222 union
Select 124, 222 union
Select 125, 222 union
Select 111, 223 union
Select 111, 224

if object_ID ('#test2') is not null drop table #test2
create table #test2 (col1 int, col2 int)
Insert into #test2
Select distinct col1, min(col2) from #test group by col1


if object_ID ('#test3') is not null drop table #test3
create table #test3 (col1 int, col2 int)
Insert into #test3
Select min(col1), col2 from #test2 group by col2

Select * from #test3

View Replies !

---

Duplicates
Hi,

Is there a way to find duplicates in one field? For example my query has person_nbr and for each person_nbr on one day they could have used multiple payer_names. I want to be able to count each person_nbr one time but also I want to group by description(which is the name of the provider) and by payer name to see how many person's that the provider seen with each payer. My problem is that if the person had more than one payer they are counted twice. Is there some type of aggregate function to use the first payer in the list??

With PersonMIA (person_id,person_nbr,first_name,last_name,date_of_birth) as
(
select distinct person_id,person_nbr,first_name,last_name,date_of_birth from
(select count(*) as countenc,a.person_id,a.person_nbr,
a.first_name,a.last_name,
a.date_of_birth
from person a
join patient_encounter b on a.person_id = b.person_id
group by a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth )tmp
where tmp.countenc <=1
)
select person_nbr,payer_name,first_name,last_name,description,year(create_timestamp),create_timestamp
from
(
select distinct c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,
tmp.last_name,tmp.date_of_birth,d.payer_name,b.create_timestamp
from PersonMIA tmp
join person a on a.person_id = tmp.person_id
join patient_encounter b on a.person_id = b.person_id
join provider_mstr c on b.rendering_provider_id = c.provider_id
join person_payer d on tmp.person_id = d.person_id
where c.description = 'Leon MD, Enrique'
group by c.description,tmp.person_id,tmp.person_nbr,tmp.first_name,tmp.last_name,
tmp.date_of_birth,d.payer_name,b.create_timestamp
)tmp2
where year(create_timestamp) IN (2005,2006)
group by person_nbr,payer_name,first_name,last_name,description,create_timestamp

Thanks in Advance!
Sherri

View Replies !

---

How Do I Stop These Duplicates
I have these two tables and I cant prevent duplicates.
SEARCH
Item
ItemID
Info

CATEGORYDATA
CategoryID
ItemID

SELECT DISTINCT SEARCH.ItemId, SEARCH.Item, CATEGORYDATA.CategoryId
FROM SEARCH
INNER JOIN CATEGORYDATA
ON SEARCH.ItemID = CATEGORYDATA.ItemID
And I get something like:ItemID Item CategoryID
1 item1 1
3 item3 1
1 item1 2 <---duplicate
1 item1 3 <---duplicate
2 item2 3
4 item4 3
Thanks in advance

View Replies !

---

Help With A Join And Duplicates?
Hi All,I am banging my head against a brick wall over this problem, so anyhelp in the correct direction would be muchly appreciated!I have 2 SQL (MS SQL) server tables, realated to -a Property,Sales of that property.A property is uniquely identifed by its Roll, valuation Number andSuffix (not my choosing).Each property can only appear in the property table once, and can onlyhave 1 assessment - but can have multiple sales (ie - over theannalysis period the same property can sell more than once).There is approximatly 19000 properties relating to about 8000 sales.When creating a query to list property and most recent sale (if thereis any) I end up with somthing like this -SELECT [roll], [valuation], [suffix], [sale date]FROM [property]LEFT JOIN [sales]ON[property].[roll] = [sales].[roll] AND[property].[valuation] = [sales].[valuation] AND[property].[suffix] = [sales].[suffix](table names simplifed).I get rows where there is all the property data there, but sale date(etc.) is null (as I would expect from a left join), but the problem is- when there is more than 1 sale for a property it pulls out anothercopy of the property data.In short, because of that I come out with more records than properties.ie -roll valuation suffix sale date12 456789 A 1/1/200312 788988 B NULL14 123456 A 1/1/200314 123456 A 1/1/2004(Note - the last two are the same property).I didn't know that the left join can affect both joined tables!Is there any way around this? Any suggestions/hints in the rightdirection would be very much appreciated!THANKS!

View Replies !

---

Delete Duplicates
I'm having trouble figuring out how to delete some _almost_ duplicaterecords in a look-up table. Here's the table:CREATE TABLE [user_fields] ([fKEY] [char] (16) NOT NULL ,[SEQUENCE] [char] (2) NOT NULL ,[FIELD_LABEL] [varchar] (20) NULL ,[FIELD_VALUE] [varchar] (50) NULL ,[EXPORT_DATE] [datetime] NULL ,CONSTRAINT [PK_user_fields] PRIMARY KEY CLUSTERED([fKEY],[SEQUENCE])CONSTRAINT [FK_USRFLD_INV_DOCID] FOREIGN KEY([fKEY]) REFERENCES [OTHER_TABLE] ([PKEY]))Some values:fKEY SEQUENCE FIELD_LABEL FIELD_VALUE----------------------------------------------------------8525645200692B8919Co. ID #8525645200692B8920Co. ID #8525645200692B8921Co. ID #8525645200692B8913Co/Div/Dept8525645200692B8914Co/Div/Dept8525645200692B8915Co/Div/Dept8525645200692B8916Division8525645200692B8917Division8525645200692B8918Division8525645200692B8910Group8525645200692B8911Group8525645200692B8912Group8525645200692B891 HR ContactJOHN NOVAK8525645200692B892 HR ContactJOHN NOVAK8525645200692B893 HR ContactJOHN NOVAK8525645200692B8924Job Location8525645200692B8922Job Location8525645200692B8923Job Location8525645200692B894 Manager8525645200692B895 Manager8525645200692B896 Manager8525645200692B897 Recruiter8525645200692B898 Recruiter8525645200692B899 Recruiter85256D740081C3A413Co. ID #85256D740081C3A414Co. ID #85256D740081C3A410Co/Div/Dept85256D740081C3A49 Co/Div/Dept85256D740081C3A411Division85256D740081C3A412Division85256D740081C3A48 Group85256D740081C3A47 Group85256D740081C3A42 HR ContactDiana Tarry85256D740081C3A41 HR ContactDiana Tarry85256D740081C3A415Job Location85256D740081C3A416Job Location85256D740081C3A43 Manager85256D740081C3A44 Manager85256D740081C3A45 Recruiter85256D740081C3A46 RecruiterNote that fKEY 8525645200692B89 has three of every FIELD_LABEL, andfKEY 85256D740081C3A4 has two. Both, however, should have only one.Unfortunately, when I do a slect ... having count(*) > 1, I have nearly900 different fKEYs with some variation of this problem.It's just not coming to me how to delete the duplicates (except forsequence). I don't care which of the sequence values I keep but as amatter of preference I tried to do something using max(sequence) but,so far, everything I've tried deletes all records for any given fKEY.Help?Thanks.Randy

View Replies !

---

Best Way To Add To DB While Checking For Duplicates
Situation:Day 1Table contains 100 items of actions imported via FTP. One of the fieldsin the table can be updated to reflect an assigned unit code.Day 2Actions that may be duplicates of the table from Day 1 (with theexception of the updated assigned unit field) are imported forinclusion in the Table from Day 1.Question- What is the best way to insure that any new items are addedto the table and that no duplicates are added at the sametime?Assistance would be appreciated.

View Replies !

---

Deleting More Duplicates
Hello,I have a stored procedure that deletes duplicatesin one table:.....ASBEGINDELETE FROM mytableWHERE Id IN(SELECT Max(id)from mytablegroup by date, idsenshaving count(*) >1)ENDsometimes it happens that I have >2 rows with duplicated values.How can I write a new stored procedure that delete all rows withduplicated infomrations (leaving only one row with those values)?ThanksM.A.

View Replies !

---

One To Many Join Causes Duplicates
When I run the attached query, I get duplicates when there is one tomany relationship between tableA and tableB. The query, tested schemaand the result is attached. Sorry for the long post.Here is tested Schema and Data inserts.----------------------create table TestTblA(ShipDate datetime,CPEID varchar(30),phonenum char(14))gocreate table TestTblB(CPEID varchar(30),itemID varchar(30),active char(1))gocreate table TestTblC(itemID varchar(30),descr varchar(50))goinsert into TestTblA values (getdate(),'TWMUA','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUA','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUB','(408)-555-1213')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1211')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1212')insert into TestTblA values (getdate(),'TWMUC','(408)-555-1213')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1211')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1212')insert into TestTblA values (getdate(),'WWEXI','(408)-555-1211')insert into TestTblB values ('TWMUA','1000-000043-000','Y')insert into TestTblB values ('TWMUB','1000-100002-001','Y')insert into TestTblB values ('TWMUC','1000-200005-000','Y')insert into TestTblB values ('WWEXI','1000-401001-000','Y')insert into TestTblB values ('WWEXI','1000-401002-000','Y')insert into TestTblC values ('1000-000043-000','descrUA')insert into TestTblC values ('1000-100002-001','descrUB')insert into TestTblC values ('1000-200005-000','descrUC')insert into TestTblC values ('1000-401001-000','descrWW')insert into TestTblC values ('1000-401002-000','descrWW')----------------Query follows------------SELECT A.ShipDate,A.CPEId,ItemId = CASEWHEN A.CPEId = 'TWMUA' THEN 'New - Single User'WHEN A.CPEID = 'TWMUB' THEN 'New - Multi User'WHEN A.CPEID = 'TWMUC' THEN 'New - Triple User'When B.ITEMID is NULL THEN 'Unknown'When B.ITEMID = ' ' THEN 'Unknown'else B.ItemIdend,MODEL_NO = CaseWhen B.ITEMID = '1000-000043-000' Then rtrim(C.DESCR)When B.ITEMID = '1000-100002-001' Then rtrim(C.DESCR)When B.ITEMID = '1000-200005-000' Then rtrim(C.DESCR)WHEN A.CPEId = 'TWMUA' THEN '1100'WHEN A.CPEID = 'TWMUB' THEN '1100'WHEN A.CPEID = 'TWMUC' THEN '1000SW'When C.DESCR is NULL THEN 'Unknown'else 'Unknown'end ,COUNT(A.phonenum)FROM TestTblA A LEFT OUTER JOIN TestTblB B ON A.CPEID=B.CPEID andb.active = 'Y'LEFT OUTER JOIN TestTblC C ON B.ItemId=C.ITEMIDGROUP BY A.ShipDate,A.CPEId,B.ItemId,C.DESCRORDER BY A.ShipDate,A.CPEId,B.ItemId,C.DESCR---- end of queryThe result (modified the output format to fit a single line)ShipDate CPEId ItemId MODEL_NO Count2003-07-18 TWMUA New - Single User descrUA 22003-07-18 TWMUB New - Multi User descrUB 32003-07-18 TWMUC New - Triple User descrUC 32003-07-18 WWEXI 1000-401001-000 NULL 32003-07-18 WWEXI 1000-401002-000 NULL 3** The problem **I need WWEXI or any similar entry to only show once, it shows twice.Thanks for your help.

View Replies !

---

Select Without Duplicates
my data is like so.IdDate Transaction5459/24/2003 3:01:08 PM13051:105469/24/2003 3:03:30 PM13051:105389/24/2003 2:53:31 PM13051:10025399/24/2003 2:54:57 PM13051:10021369/24/2003 10:08:45 AM13051:1011379/24/2003 10:08:47 AM13051:101I wanna run a query that gives meIdDate Transaction5459/24/2003 3:01:08 PM13051:105389/24/2003 2:53:31 PM13051:10021369/24/2003 10:08:45 AM13051:101The first record of the duplicates

View Replies !

---

Removing Duplicates
HiI have inherited a web app with the following table structure, and need toproduce a table without any duplicates. Email seems like the best uniqueidentifier - so only one of each e-mail address should be in the table.Following http://www.sqlteam.com/item.asp?ItemID=3331 I have been able toget a duplicate count working:select Email, count(*) as UserCountfrom dbo.Membersgroup by Emailhaving count(*) > 1order by UserCount descBut the methods for create a new table without duplicates fail. My code forthe 2nd method is:sp_rename 'Members', 'temp_Members'select distinct *into Membersfrom temp_MembersTable....CREATE TABLE [dbo].[Members] ([MemberID] [int] IDENTITY (1, 1) NOT NULL ,[Username] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Password] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[Email] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Title] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,[FirstName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Surname] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Address1] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[Address2] [varchar] (35) COLLATE Latin1_General_CI_AS NOT NULL ,[City] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Country] [varchar] (25) COLLATE Latin1_General_CI_AS NOT NULL ,[Profession] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,[Publication] [varchar] (40) COLLATE Latin1_General_CI_AS NOT NULL ,[DateAdded] [smalldatetime] NOT NULL ,[SendMail] [smallint] NOT NULL) ON [PRIMARY]GOThanks B.

View Replies !

---

Finding Duplicates
I have a company table and I would like to write a query that will return tome any duplicate companies. However, it is a little more complicated thenjust matching on exact company names. I would like it to give me duplicateswhere x number of letters at the beginning of the company name match AND xnumber of letters of the address match AND x number of letters of the citymatch. I will be doing this in batches based on the first letter of thecompany name. So for example I will first process all companies that startwith the letter "A".So for all "A" companies I want to find companies where the first 5 lettersin the company name match and the first 5 characters of the address fieldmatch and the first 5 characters of the city match. THANKS!!!

View Replies !

---

SQL Eliminate Duplicates
I am working with SQL 8.00. I have the following tablesTABLE ClientInfoCheckNum Account Name Addr1 City State Zip---------------------------------------------------------------------12345 11111 John 123 Mary St Miami FL3313954321 22222 Mary 321 River Side Clifton NJ0705598765 33333 Tom 12 Main St Miami FL33139and TABLE ClientAcctCheckNumAccount Cost Credit Notes---------------------------------------------------------------------12345 11111 1Yes Great12345 11111 11Yes Well12345 11111 111No Bad54321 22222 2Yes Fine54321 22222 22No OK98765 33333 3Yes I like itThis the end result that I want.CheckNum Account Name Addr1 City State ZipSUM ofMax(Notes)of Cost orMin(Notes)-----------------------------------------------------------------------------12345 11111 John 123 Mary St Miami FL33139 123Great54321 22222 Mary 321 River St Clifton NJ07055 24 Fine98765 33333 Tom 12 Main St Miami FL33139 3I like itI need to avoid duplicate rows(Note only if the Account field areequal).I need to get the fields shown above including the SUM ofClientAcct.Cost and the MAX or Min of ClientAcct.Notes.I have searched the web andhave tried DISTINCT, UNION, GROUP, COUNT(*) = 1 AND COUNT(*) <> 1 butI can't get the correct results.This statement give me too many rows(duplicate)--------------------------------------SELECT [Name], ClientInfo.account, addr1, City, State, ZIP, COST,Notes from ClientInfo JOIN ClientAcct onClientInfo.CheckNum=ClientAcct.CheckNumDoes any body have a solution to this?Thanks in advanceJulio

View Replies !

---

Finding Duplicates
I am trying to complete an insert from query but the problem is I have duplicates, so I'm getting an error message. So to correct it I am creating a Find Duplicates statement in the Query analyzer but Its not working can someone tell me whats wrong with this statement (by the way I'm in SQL 2000 Server)

thank you

SELECT EmployeeGamingLicense [TM#]AS [TM# Field], Count([TM#])AS NumberOfDups
FROM TERMINATION
GROUP BY [TM#]
HAVING Count([TM#])>1;
GO

View Replies !

---

Self-Join Duplicates - Help!
Can some kind person out there please help me, I've been stuck on this for daaaa-y-s.

I have a database that allows users to search for pdf's of technical drawings.

Basically I have one huge table with multiple columns, which the user can only search on any combination of one of these two columns
"drawing_series" eg 0100, 0046, 1000
"drawing_number" eg 0076000, 0000123, 0000004

There is also a Revision column(which the user can't see) that goes up by 1 each time a drawing has been modified and resubmitted to the database.
"revision" eg 01, 02, 03, ....... 99

So a search on 0046 series might pull back drawings

0046-0010000-01
0046-0010000-02
0046-0010000-03
0046-0076000-01
0046-0076888-01
0046-0076888-02

The problem is that I only want drawings with the highest revisions returned eg

0046-0010000-03
0046-0076000-01
0046-0076888-02

The code below worked like a charm in the test stages pulling back a few hundred records but now that i've uploaded 10's of thousands of records to the DB the whole lot dies if the search result pulls back more than a few thousand records.

SELECT * FROM dbo.Drawing_Database
where dbo.Drawing_Database.revision=(select max(revision) from dbo.Drawing_Database self where self.drawing_series + self.drawing_number = dbo.Drawing_Database.drawing_series + dbo.Drawing_Database.drawing_number) Drawing_Series like '0046' order by Drawing_Series, Drawing_Number

There must be a simpler way of doing this as i can pull out duplicate series + numbers using " HAVING Count(*)>1" but dont know where to go from there.

Help!
TheMaster

View Replies !

---

Eliminating Duplicates
Have a pretty simple wuestion but the answer seems to be evading me:

Here's the DDL for the tables in question:


CREATE TABLE [dbo].[Office] (
[OfficeID] [int] IDENTITY (1, 1) NOT NULL ,
[ParentOfficeID] [int] NOT NULL ,
[WebSiteID] [int] NOT NULL ,
[IsDisplayOnWeb] [bit] NOT NULL ,
[IsDisplayOnAdmin] [bit] NOT NULL ,
[OfficeStatus] [char] (1) NOT NULL ,
[DisplayORD] [smallint] NOT NULL ,
[OfficeTYPE] [varchar] (10) NOT NULL ,
[OfficeNM] [varchar] (50) NOT NULL ,
[OfficeDisplayNM] [varchar] (50) NOT NULL ,
[OfficeADDR1] [varchar] (50) NOT NULL ,
[OfficeADDR2] [varchar] (50) NOT NULL ,
[OfficeCityNM] [varchar] (50) NOT NULL ,
[OfficeStateCD] [char] (2) NOT NULL ,
[OfficePostalCD] [varchar] (15) NOT NULL ,
[OfficeIMG] [varchar] (100) NOT NULL ,
[OfficeIMGPath] [varchar] (100) NOT NULL ,
[RegionID] [int] NOT NULL ,
[OfficeTourURL] [varchar] (255) NULL ,
[GeoAreaID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[OfficeBrandedURL] [varchar] (255) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeManagement] (
[OfficeID] [int] NOT NULL ,
[PersonnelID] [int] NOT NULL ,
[JobTitleID] [int] NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[SeqNBR] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[OfficeMls] (
[OfficeID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[OfficeMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Personnel] (
[PersonnelID] [int] IDENTITY (1, 1) NOT NULL ,
[PersonnelDisplayName] [varchar] (100) NOT NULL ,
[FirstNM] [varchar] (50) NOT NULL ,
[PreferredFirstNM] [varchar] (50) NOT NULL ,
[MiddleNM] [varchar] (50) NOT NULL ,
[LastNM] [varchar] (50) NOT NULL ,
[PersonalTaxID] [varchar] (9) NOT NULL ,
[HireDT] [datetime] NOT NULL ,
[TermDT] [datetime] NOT NULL ,
[HomePhoneNBR] [varchar] (15) NULL ,
[HomeADDR1] [varchar] (50) NOT NULL ,
[HomeADDR2] [varchar] (50) NOT NULL ,
[HomeCityNM] [varchar] (50) NOT NULL ,
[HomeStateCD] [char] (2) NOT NULL ,
[HomePostalCD] [varchar] (15) NOT NULL ,
[PersonnelLangCSV] [varchar] (500) NOT NULL ,
[PersonnelSlogan] [varchar] (500) NOT NULL ,
[BGColor] [varchar] (50) NOT NULL ,
[IsEAgent] [bit] NOT NULL ,
[IsArchAgent] [bit] NOT NULL ,
[IsOptOut] [bit] NOT NULL ,
[IsDispOnlyPrefFirstNM] [bit] NOT NULL ,
[IsHideMyListingLink] [bit] NOT NULL ,
[IsPreviewsSpecialist] [bit] NOT NULL ,
[AudioFileNM] [varchar] (100) NULL ,
[iProviderID] [int] NOT NULL ,
[DRENumber] [varchar] (10) NOT NULL ,
[AgentBrandedURL] [varchar] (255) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL ,
[IsDisplayAwards] [bit] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[PersonnelMLS] (
[PersonnelID] [int] NOT NULL ,
[SourceID] [int] NOT NULL ,
[AgentMlsNBR] [varchar] (20) NOT NULL ,
[CreateDT] [datetime] NOT NULL ,
[UpdateDT] [datetime] NOT NULL ,
[CreateByID] [varchar] (50) NOT NULL ,
[UpdateByID] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[Office] ADD
CONSTRAINT [FK_Office_OfficeProfile] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[OfficeProfile] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[Office] nocheck constraint [FK_Office_OfficeProfile]
GO

ALTER TABLE [dbo].[OfficeManagement] ADD
CONSTRAINT [FK_OfficeManagement_LookupJobTitle] FOREIGN KEY
(
[JobTitleID]
) REFERENCES [dbo].[LookupJobTitle] (
[JobTitleID]
),
CONSTRAINT [FK_OfficeManagement_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_OfficeManagement_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) ON DELETE CASCADE
GO

alter table [dbo].[OfficeManagement] nocheck constraint [FK_OfficeManagement_Office]
GO

ALTER TABLE [dbo].[OfficeMls] ADD
CONSTRAINT [FK_OfficeMls_Office] FOREIGN KEY
(
[OfficeID]
) REFERENCES [dbo].[Office] (
[OfficeID]
) NOT FOR REPLICATION
GO

alter table [dbo].[OfficeMls] nocheck constraint [FK_OfficeMls_Office]
GO

ALTER TABLE [dbo].[PersonnelMLS] ADD
CONSTRAINT [FK_PersonnelMLS_Personnel] FOREIGN KEY
(
[PersonnelID]
) REFERENCES [dbo].[Personnel] (
[PersonnelID]
) NOT FOR REPLICATION
GO

alter table [dbo].[PersonnelMLS] nocheck constraint [FK_PersonnelMLS_Personnel]
GO





Here's the query I'm having trouble with:

SELECT distinct Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID AS OfficeBoard,
PersonnelMLS.SourceID AS AgentBoard
FROM Personnel INNER JOIN
OfficeManagement ON
Personnel.PersonnelID = OfficeManagement.PersonnelID
INNER JOIN
Office ON OfficeManagement.OfficeID = Office.OfficeID
INNER JOIN
OfficeMls ON Office.OfficeID = OfficeMls.OfficeID
INNER JOIN
PersonnelMLS ON Personnel.PersonnelID = PersonnelMLS.PersonnelID
where officemls.sourceid <> personnelmls.sourceid
and office.officenm not like ('%admin%')
group by PersonnelMLS.SourceID,
Personnel.PersonnelID,
Personnel.FirstNM,
Personnel.LastNM,
Office.OfficeNM,
Office.OfficeID,
OfficeMls.SourceID
order by office.officenm

What I'm trying to retrieve are those agents who have source id's that are not in the Office's domain of valid source id's. Here's a small portion of the results:

PersonnelID FirstNM LastNM OfficeNM OfficeID OfficeBoard AgentBoard
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------- -----------
18205 Margaret Peggy Quattro Aventura North 650 906 908
18205 Margaret Peggy Quattro Aventura North 650 918 908
15503 Susan Jordan Blackburn Point 889 920 909
15503 Susan Jordan Blackburn Point 889 921 909
15503 Susan Jordan Blackburn Point 889 921 920
15279 Sandra Humphrey Boca Beach North 890 917 906
15279 Sandra Humphrey Boca Beach North 890 906 917
15279 Sandra Humphrey Boca Beaches 626 917 906
15279 Sandra Humphrey Boca Beaches 626 906 917
13532 Michael Demcho Boca Downtown 735 906 917
14133 Maria Ford Boca Downtown 735 906 917
19126 Michael Silverman Boca Glades Road 736 917 906
18920 Beth Schwartz Boca Glades Road 736 906 917

If you take a look at Sandra Humphries, you'll see she's out of office 626. Office 626 is associated with source id's 907 and 916. Sandra Humphries is also associated with those two source id's , but she shows up in the results.

I know this was AWFULLY long winded, but just wanted to make sure made myself as clear as possible.

Any help would be greatly appreciated.

Thanks in advance!

View Replies !

---

Finding Duplicates - Is This Right?
I've done a search and I THINK I've got my head round this, but I'd be very grateful if someone could reassure me:

SELECT Email FROM List1 WHERE EXISTS (
SELECT Email FROM List2 WHERE List2.Email= List1.Email
) AND List1.Email <> '44'

That will give me every email address from list one that (a) appears in list two, and (b) isn't '44'. Right?

And to find all the emails from List1 that DON'T occur in List2 (and aren't '44'), I just put "NOT" in front of "EXISTS". Right?

Sorry for asking an obvious question but I'm having a real mental block here. :o

View Replies !

---

Select All Duplicates
Just wanted to ask how to get all the duplicates records in a table. If I have say the following:
col1 col2 col3 col4 col5
1 A1 ABC A21 AJ
1 A1 ABC A21 AJ
1 A2 ABC A21 AJ

The query should return the first 2 identical rows. I tried the following form but as you can see it has flaw that it gets the 3rd row as well simply because the outer select uses col1 as a condition which could belong to a "not completely" identical row.
The inner select results in the distinct duplicate rows (2 in the table above, either of row 1 or 2 and row 3).

select *
from table1
where col1 IN
(select col1
from table1
group by col1, col2, col3, col4, col5
having count(*) > 1
)

Thank you for any help and have a great week end!

View Replies !

---

Archive Duplicates
Hi,

Does anyone out there have a solution to duplicate data enteries when archiving data using the Database/Object Transfer in
Microsoft SQL Enterprise Manager ?

No matter what combination of Copy Data and Replace Existing Data I use I still seem to get
duplicate enteries.

If I use a backup and restore technique to do my Archives will this overwrite any data that previously existed in the database I am
restoring to ?

What is the best method of Archiving when appending data from one database to another and not wanting to overwrite or duplicate enteries ?

I hope someone can help on this. I have tried many options and have not come to a proper solution yet.

Thanks in advance,
Alan

View Replies !

---

SQL To Find Duplicates
Hello All,
Can someone please post SQL that will return only values from a column that has more than one occurrence of the value in the column. The problem is that I have 1500 distinct values and 1503 records and I need to located the duplicate values. Any help would be appreciated.

Thanks,
Terry

View Replies !

---

Finding Duplicates In SQL
I have a customer database with the following structure:

Dept ID (int)
Section (varchar)

I need to find only occurrences of a section (eg Admin) where the section name has a record in Dept 1,2 and 3 - only return the result if the record for Admin is associated will these depts.

Example Data:

Dept Section
1 Admin
1 Finance
1 Marketing
2 Admin
2 Sales
2 Marketing
3 Admin
3 Sales
3 Finance

The query would only return 'Admin' since this is the only Section that is represented in Departments 1,2 + 3.

I am not in control of the data design and can't create a bitwise field to achieve this.

tia,
Steve

View Replies !

---

Indexing And Duplicates
Hi,

I’m using SQL Server 2000. I have a table called Contacts and I would like to be able to have the UserID as an indexed column and to ignore duplicates. I set up the following properties within my SQL Server database table:

Table Name: Contacts
Selected Index: IX_UserID
Column Name: UserID
Order: Ascending
Create Unique
Index
Ignore Duplicate Key


Every time I try to enter duplicates for the UserID column; I get an error that says, “Cannot enter duplicate key row in object ‘Contacts’. Can anyone explain this? Is it possible to create an index column with duplicate data?

Thanks,
Denise

View Replies !

---

Removing The Duplicates
I need to remove the duplicates from a table. If a record exits 4 times in a
table I need to delete 3 records and retain only one occurance of that.

I need a query to do this.

Can anybody help.

-Rajesh

View Replies !

---