Transact SQL :: How To Find Duplicates For Multiple Fields
Oct 26, 2015
I have a table that exists of Serial, MSDSID and other fields.
What I need to check is to see if there are Serial numbers that multiple MSDSID numbers.
So if I have
Serial MSDSID
001 23
002 24
003 25
004 23
005 26
006 24
The results would be
Serial MSDSID Count
001 23 2
004 23 2
002 24 2
006 24 2
View 12 Replies
ADVERTISEMENT
Oct 27, 2015
I have a table that has Serial numbers and MSDSID numbers and many other fields in the table.
What I need to figure out is if the table contains a distinct Serial number with different MSDSIDs.
So If I have in the table
Serial MSDSID Date Size...
001 20 1/1/2015 5
002 21 1/1/2015 3
001 22 2/1/2015 1
003 21 3/1/2015 1
004 23 1/15/2015 5
003 22 1/20/2015 6
004 23 2/21/2015 5
002 21 4/25/2015 4
I would like the results to show:
Serial Count
001 2 the count is 2 because Serial 001 has an MSDSID of 20 and 22
002 1 the count is 1 because Serial 002 only has MSDSID 21
003 2 the count is 2 because Serial 003 has an MSDSID of 21 and 22
004 1 the count is 1 because Serial 002 only has MSDSID 23
It would be even better if the results just showed where the count is greater than 1.
View 5 Replies
View Related
Nov 18, 2015
I need to make a selection on join datasets with 2 conditions and populate the results in another dataset(Report).It is working with the fist condition "AccountingTypeCharacteristicCodeId = 3"...
INSERT INTO SurveyInterface.tblLoadISFNotification (OperatingEntityNumber, SDDS, SurveyCodeId, QuestionnaireTypeCodeId, ReferencePeriod, DataReplacementIndicator, PrecontactFlag, SampledUnitPriority)
SELECT ISF.OperatingEntityNumber
,[SDDS]
,[SurveyCodeId]
,[QuestionnaireTypeCodeId]
,[ReferencePeriod]
,[DataReplacementIndicator]
[code]....
Know I also want to add in that new dataset(report) all the duplicates of concatenated variables
ISF.OperatingEntityNumber/ISF.QuestionnaireTypeCodeId
GROUP BY ISF.OperatingEntityNumber, ISF.QuestionnaireTypeCodeId
View 4 Replies
View Related
Jul 2, 2015
I am using MS SQL 2012. I have a table that contains all the data that I need, but I need to summarize the data and also add up decimal fields while at it. Then I need a total of those added decimal fields. My data is like this:
I have Providers, a unique ID that Providers will have multiples of, and then decimal fields. Here are my fields:
ID, provider_name, uniq_id, total_spent, total_earned
Here is sample data:
1, Harbor, A07B8, 500.00, 1200.00
2, Harbor, A07B8, 400.00, 800.00
3, Harbor, B01C8, 600.00, 700.00
4, Harbor, B01C8, 300.00, 1100,00
5, LifeLine, L01D8, 700.00, 1300.00
6, LifeLine, L01D8, 200.00, 800.00
I need the results to be just 3 lines:
Harbor, A07B8, 900.00, 2000.00
Harbor, B01C8, 900.00, 1800.00
LifeLine, L01D8, 900.00, 2100.00
But then I would need the totals for the Provider, so:
Harbor, 1800.00, 3800.00
View 3 Replies
View Related
Oct 29, 2015
I have the folloiwng fields:
ProbationLengthYears AS Decimal
ProbationLengthMonths AS Decimal
ProbationLengthDays AS Decimal
Sample Data:
ProbationLengthYears = 2.00
ProbationLengthMonths 0.00
ProbationLengthDays 0.00
I have successfully concatenated and converted all three fields into one field as Follow:
SELECT
CONVERT(varchar(10),CAST(cc2.ProbationLengthYears AS INT)) + ' Year(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthMonths AS INT)) + ' Month(s)' + ' ' +
CONVERT(varchar(10),CAST(cc2.ProbationLengthDays AS INT)) + ' Day(s)'
The result is:
2 Year(s) 0 Month(s) 0 Day(s)
Ideally I would like the result to be as follow because the probation months and days are 0:
2 Year(s).
View 4 Replies
View Related
Dec 5, 2015
I have a table where I need to concatenate all values into one field separated by a comma. If the field is null display a blank value. This is my table structure and example output
Create Table #read
(
id int
,field1 varchar(100)
,field2 varchar(100)
,field3 varchar(100)
,field4 varchar(100)
[code]...
View 5 Replies
View Related
Sep 12, 2014
I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:
SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;
This is a snap shot of the output:
ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS
[Code] ....
What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?
View 3 Replies
View Related
Apr 15, 2008
Hi,
I have table which stores the fund name and its data. We get quarterly information from the fund co. Suppose if the user wants to add a fund thats not in our database we let then add a ClientFundId and a FundName. But may be after sometime the fund company may add that fund in the next quarter.. So how do i get rid of Duplicated Data..
In the ClientFundId column we can a 9 letter Aplhanumeric or a 5 letter character but if the fund co.. provides those values the 5 letter characters are stored in Ticker column and the 9 letter words are stored in Cusip column.. So i just wrote this query hoping i could retrieve the duplicate values but it didnt list any..but i found one this is my query..
Select
FundId,
Cusip,
Ticker,
ClientFundId,
FundName,
ShortName
From Fund
Where
ClientFundId = Ticker
or
ClientFundId = Cusip
Any help will appreciated
Thanks
Karen
View 18 Replies
View Related
Jul 19, 2004
Hi
I am trying to find when a name has been entered more than once into 1 database table.
I'm currently doing something like this (can't remember exactly, not at work)
SELECT COUNT(*) AS Cnt, Name
FROM tblTable
GROUP BY Name
ORDER BY Cnt Desc
This brings back all the Names in the database and tells me which are duplicates but I want to just have the results of the duplicate values and not the single values.
Hope you can help.
Thanks
View 2 Replies
View Related
Dec 11, 1999
does someone have a querry to display the duplicate records in a table.
Table:
zipcode dma
My data upload is failing because there is a primary key on zipcode and the source data (42k records) has about 50 duplicate zipcode records in it. It is possible that there is a unique combo of zipcode / dma but I need to identify the duplicate records to determine that.
View 1 Replies
View Related
May 28, 2008
I have this script bellow which does what it is supposed to. However it only outputs the cust_id. I want it to show all the columns in the table. How would I do this?
SELECT cust_id
FROM cust_table
WHERE cust_name in ('Billy','John') and rownum < 100
GROUP BY cust_id
HAVING COUNT(*) > 1;
View 6 Replies
View Related
Aug 8, 2007
Hi everybody I need help on finding duplicates and deleting the duplicate record depending on name and fname , deleting the duplicates and leaving only the first one.
my PERSON table is this below:
ID name fname ownerid id2
1 a b
2 c c
3 e f
4 a b 1 10
5 c c 2 11
I have this query below that returns records 1 and 4 and 2 and 5 since they have the same name and fname
select * from ( Select name ,fname, count(1) as cnt from PERSON group by
name,Fname ) where cnt > 1
ID name fname ownerid id2
1 a b
4 a b 1 10
2 c c
5 c c 2 11
With this result I need to delete the second record of each group but update the first records with the ownerid and id2 of the second record that would be deleted... I don't know how to proceed with this..
thanks
alex
View 5 Replies
View Related
Sep 26, 2014
I have this query below that I created to do a count, but I don't think this is what I needed.
I need to find the duplicates. Example, if
CLI_ID1 12345 has 4 CLIP records, each CLIP record should have a different CLIP rank. I need to find scenarios where 2 (or more) of the CLIP records have the same CLIP RANK. If there are duplicate CLIP_RANKs within the same CLI_ID,
Select Distinct
cli_id1, count(clip_rank) countrank
FROM impact.dbo.CLI
LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1
left join
impact.dbo.clip ON cli_id1 = clip_id1
Where (clio_trm = '' or clio_trm = NULL or clio_trm is null)
group by cli_id1
order by cli_id1
View 1 Replies
View Related
Jul 31, 2013
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
View 5 Replies
View Related
Nov 2, 2015
I am having a main table and temptable.
Every sunday, new data will be loaded from temptable to main table. I have to make sure that, duplicates does not get loaded from temptable to maintable.
For example, if last sunday a record gets loaded from temp to main. If this sunday also the same record is present then it means that is a duplicate.
The duplicate is decided on below scenario
select 'CodeChanges: ', count(*) from CodeChanges a, CodeChanges_Temp b
where a.AccountNumber = b.AccountNumber and
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
a.HexaId = b.HexaId and
[Code] ...
Yesterday (Sunday) , data from temp got loaded onto maintable but with duplicates.
There is a log which just displays number of duplicates.
Yesterday the log displayed 8 duplicates found. I need to find out the 8 duplicates which got loaded yesterday and delete it off from main table.
There is a column in both tables which is 'creation date and time'. Every Sunday when the load happens this column will have that day's date .
Now i need to find out what are all the duplicates which got loaded on this sunday.
The total rows in temp table is : 363
No of duplicates present is : 8
I used below query to find out the duplicates but it is returning all the 363 rows from the maintable instead of the 8 duplicates.
Select 'CodeChanges: ', * from CodeChanges a where
exists
( Select 1 from CodeChanges_Temp b where
a.HexaNumber = b.HexaNumber and
a.HexaEffDate = b.HexaEffDate and
[Code] ...
Need finding the duplicate records which has creation date time as '2015-11-01 00:00:00.000' and all the above columns mentioned in the query matches.
Example
Few colums only metioned below
creationdateandtime HexaNumber HexaCode
1. 1987-10-01 00:00:00.000 5 3
2. 2015-11-01 00:00:00.000 5 3
So here the second record is duplicate. This is what I am trying to find.
View 4 Replies
View Related
Aug 22, 2007
My basic situation is this - I ONLY want duplicates, so the oppositeof DISTINCT:I have two tables. Ordinarily, Table1ColumnA corresponds in a one toone ratio with Table2ColumnB through a shared variable. So if I queryTableB using the shared variable, there really should only be onrecord returned. In essence, if I run this and return TWO rows, it isvery bad:select * from TableB where SharedVariable = 1234I know how to join the tables on a single record to see if this is thecase with one record, but I need to find out how many, among possiblymillions of records this affects.Every record in Table1ColumnA (and also the shared variable) will beunique. There is another column in Table1 (I'll call itTable1ColumnC) that will be duplicated if the record in Table2 is aduplicate, so I am trying to use that to filter my results in Table1.I am looking to see how many from Table1 map to DUPLICATE instances inTable2.I need to be able to say, in effect, "how many unique records inTable1ColumnA that have a duplicate in Table1ColumnC also have aduplicate in Table2ColumnB?"Thanks if anyone can help!-- aknoch
View 1 Replies
View Related
May 6, 2015
I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.
View 5 Replies
View Related
Jun 8, 2015
I am doing some audit and i have below query, how can i get rid of duplicates from the below query any T SQL to get rid of duplicates...
I am using SP_Who2 and sql server Audit for auditing all data happening on sql server databases and dumping them to tables Audit_DBAudit abd Audit_sp_who2 and from then i am trying to get data which is not repeating/duplicate ...
SELECT
A.ProgramName
,a.HostName,[Server_principal_name],[Server_instance_name],[Database_name],[Object_name],F.Statement
FROM Audit_DBAudit as F
Join [Audit_sp_who2] AS a
on LTRIM(RTRIM(F.server_principal_name))=LTRIM(RTRIM(A.Login))
View 11 Replies
View Related
Sep 1, 2015
I have table with columns as ID, DupeID1, DupeID2. ID column is unique. DupeID1 and DupeID2 -- the combination should only be there once. I don't want reverse combination of duplicates, i.e. DupeID2, DupeID1 in the table. How can I delete the reverse duplicates from this table?
View 10 Replies
View Related
Jun 2, 2015
I have an existing stored table with duplicate rows that I want to delete.Using a cte gives me
WITH CTE AS
(
SELECT rn = ROW_NUMBER()
OVER(
PARTITION BY employeeid, dateofincident, typeid, description
ORDER BY Id ASC), *
FROM dbo.TableName
)
DELETE FROM cte
WHERE rn > 1
This is what I want to do basically. But this is only deleting in my CTE, is there anyway I can update my existing table "TableName" with this, without using temp tables?
View 4 Replies
View Related
Jul 27, 2015
How would you differentiate duplicates in a table without deleting.
I have a table name ANGEL that contains duplicate value and i want to append a letter V to the duplicates instead of deleting them.
View 5 Replies
View Related
Aug 11, 2014
I have a patient record and emergency contact information. I need to find duplicate phone numbers in emergency contact table based on relationship type (RelationType0 between emergency contact and patient. For example, if patient was a child and has mother listed twice with same number, I need to filter these records. The case would be true if there was a father listed, in any cases there should be one father or one mother listed for patient regardless. The link between patient and emergency contact is person_gu. If two siblings linked to same person_gu, there should be still one emergency contact listed.
Below is the schema structure:
Person_Info: PersonID, Person Info contains everyone (patient, vistor, Emergecy contact) First and last names
Patient_Info: PatientID, table contains patient ID and other information
Patient_PersonRelation: Person_ID, patientID, RelationType
Address: Contains address of all person and patient (key PersonID)
Phone: Contains phone # of everyone (key is personID)
The goal to find matching phone for same person based on relationship type (If siblings, then only list one record for parent because the matching phones are not duplicates).
View 9 Replies
View Related
Nov 20, 2015
I have this 40,000,000 rows table... I am trying to clean this 'Contacts' table since I know there are a lot of duplicates.
At first, I wanted to get a count of how many there are.
I need to compare records where these fields are matched:
MATCHED: (email, firstname) but not MATCH: (lastname, phone, mobile).
MATCHED: (email, firstname, mobile)
But not MATCH: (lastname, phone)
MATCHED: (email, firstname, lastname)
But not MATCH: (phone, mobile)
View 9 Replies
View Related
Nov 9, 2007
Ok, so right now we have a bunch of queries similar to the following:
Code:
use db001 select co#=(001), fieldvalue from tablename where fieldid = '1'
use db002 select co#=(002), fieldvalue from tablename where fieldid = '1'
what i need to find is if there are duplicates of fieldvalue and have it display all in one.
Basically right now we run all these there are 25 databases and we export everything to a file and then open it in excel. we then format it it so its just co# and fieldvalue and then look for duplicates.
i would like to avoid the whole excel part and just run a query that will display any duplicates of fieldvalue across all 25 databases in "tablename"
hope that makes sense. - Thanks
View 6 Replies
View Related
Sep 6, 2006
Hi
I currently have two tables called Book and JournalPaper, both of which have a column called Publisher. Currently the data in the Publisher column is the Publisher name that is entered straight into either table and has been duplicated in many cases. To tidy this up I have created a new table called Publisher where each entry will have a unique ID.
I now want to remove the Publisher columns from Book and JournalPaper, replace it with an ID foreign key column and move the Publisher name data into the Publisher table. Is there a way I can do this without duplicating the data as some publishers appear several times on both tables?
Any help with this will be greatly appreciated as my limited SQL is not up to this particular challenge!!!
Thanks!
View 7 Replies
View Related
Aug 11, 2015
I have a bunch of contacts that I've scored how well their names match to other contacts in the same business. I can programmatically figure out how to parse the results, but would like to know how to do this via SQL. My problem is for Business_fk 968976 I have 7 contacts. In the end I should have 4 contacts based on name match. For the business key listed Gerardo Lopez is in the ContactScore table twice for Contact keys 7355719 and 57028145. I then have two rows like so:
PossibleBusinessContactMatch_pk BusinessContact_fk Business_fk BusinessContactMatch_fk MatchTypeCode MatchScore MatchRank FirstName LastName Phone Email
------------------------------- ------------------ ----------- ----------------------- ------------- ----------- ----------- -------------------------------------------------- -------------------------------------------------- ---------- --------------------------------------------------------------------------------------------------------------------------------
1772960 57028145 968976 7355719 C 46 1 GERARDO I LOPEZ 8162214000
838834 7355719 968976 57028145 C 50 1 GERARDO
Each reference each other, and 2 is a good case, a more difficult case would have key 1 listed 10 times showing a ContactMatch_fk of 2 - 11, and then Contact_fk 2 listed 10 times with a ContactMatch_fk of 1, 3-11.I know 57028145 maps to 7355719 from the first row in the ContactScore table, so when Contact_fk of 7355719 comes up I should be able to skip it and not process that match. Hopefully that makes sense. Anyway here is the test data:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContactScore]') AND type in (N'U'))
DROP TABLE [dbo].[ContactScore];
GO
CREATE TABLE [dbo].[ContactScore]
(
[ContactScore_pk]INT NOT NULL,
[Contact_fk]INT NOT NULL,
[code]..
View 9 Replies
View Related
May 17, 2013
I'm joining several tables and when I add the last one I get duplicate results. How can I get just one for each?
select a.field, b.field, c.field
from atblname as a inner join btblname as b on a.id = b.parent_id
left outer join ctblname as c on a.id = c.parent_id
There are more than one result when joining tbl a and c, but I'm getting a reult for each of them for all results from joining a and b.
View 9 Replies
View Related
May 17, 2007
I have table 'A' containing Company information including the company zip code. I have table 'B' which is a table of ZipCodes. How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'?
Thank you
View 8 Replies
View Related
Jul 28, 2014
How can I pull out duplicate records based on certain fields?
Table called Bank
I want to pull out records that have duplicate inv_no, cus_no, amount,ordernum
Not all the fields are the same in each record. But I want the records that have these fields that are the same.
View 1 Replies
View Related
Jul 20, 2015
I have a simple report with a row grouping on teh Date and record_id from an sql query. I would like to find the row total.
The expression I have on the row is:
=IIF(Max(Fields!closing.Value) > 0, Microsoft.VisualBasic.Interaction.IIF(Max(Fields!opening.Value) > 0, IIF(Max(Fields!closing.Value) > Max(Fields!opening.Value), Max(Fields!closing.Value) - Max(Fields!opening.Value), Nothing), Nothing), Nothing)
View 2 Replies
View Related
Apr 21, 2015
I have a table with single row like below
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
Column0 | Column1 | Column2 | Column3 | Column4|
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Value0 | Value1 | Value2 | Value3 | Value4 |
Am looking for a query to convert above table data to multiple rows having column name and its value in each row as shown below
_ _ _ _ _ _ _ _
Column0 | Value0
_ _ _ _ _ _ _ _
Column1 | Value1
_ _ _ _ _ _ _ _
Column2 | Value2
_ _ _ _ _ _ _ _
Column3 | Value3
_ _ _ _ _ _ _ _
Column4 | Value4
_ _ _ _ _ _ _ _
View 6 Replies
View Related
Jul 24, 2015
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
EXEC msdb.dbo.sp_send_dbmail
@subject
= 'Job Summary',
@profile_name =
'SQL SMTP',
[code]....
View 3 Replies
View Related
Jun 21, 2006
Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex
View 2 Replies
View Related
