Return all duplicates in a single column query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return all duplicates in a single column query

Author

Topic

xclr8tr
Starting Member

2 Posts

Posted - 2009-03-21 : 20:17:26

Hi All,

This is normally easy but I have a complex query and am new enough that I can't figure out how to add the right join or subquery to my code to make it work.

It finds all the New_CustomerNumber's that have multiple TicketNumber's and is working fine, trouble is eliminating all of the distinct records in between.

I've tried all the standard queries and can make them work alone but having trouble adding to this query... please help

Select IncidentExtensionBase.New_CustomerNumber, 
IncidentBase.TicketNumber, 
SystemUserBase.FullName As Owner,
SystemUserBase1.FullName As [Created By], 
IncidentBase.CreatedOn, 
SystemUserBase1.Title, 
New_CaseProductsExtensionBase.New_name,
SubjectBase.Title, 
IncidentBase.Description, 
IncidentBase.SubjectId,
IncidentBase.StatusCode
From IncidentBase 
Inner Join IncidentExtensionBase On IncidentBase.IncidentId =
 IncidentExtensionBase.IncidentId 
Inner Join SystemUserBase SystemUserBase1 On SystemUserBase1.SystemUserId =
 IncidentBase.CreatedBy 
Inner Join SystemUserBase On IncidentBase.OwningUser = SystemUserBase.SystemUserId
Inner Join New_CaseProductsExtensionBase On IncidentExtensionBase.New_CaseProductsId =
 New_CaseProductsExtensionBase.New_CaseProductsId 
Inner Join SubjectBase On SubjectBase.SubjectId = IncidentBase.SubjectId
Where IncidentBase.CreatedOn Between '2009/03/09 00:00:00 AM' 
And '2009/03/19 00:00:00 AM' 
And 
(SubjectBase.Title = 'Customer Retention' 
Or SubjectBase.Title = 'Agent Advantage' 
Or SubjectBase.Title = 'Cancellations'
Or SubjectBase.Title = 'Collections' 
Or SubjectBase.Title = 'Refunds' 
Or SubjectBase.Title = 'Broker Sites')
ORDER BY IncidentExtensionBase.New_CustomerNumber, IncidentBase.CreatedOn

xclr8tr

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-03-22 : 09:06:40

sorry, I can't tell what you looking for. You'll need to find a better way to explain what problem you're trying to solve.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

xclr8tr
Starting Member

2 Posts

Posted - 2009-03-22 : 14:45:41

quote:
Originally posted by TG

sorry, I can't tell what you looking for. You'll need to find a better way to explain what problem you're trying to solve.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Here is a few columns of the records, I'm looking for duplicate in the "New_CustomerNumbers" with distinct "TicketNumbers" which just means that multiple people have opened a trouble ticket on this account. I can then see the time frame in which those tickets were created.

Hope this helps
xclr8tr

New_CustomerNumber	TicketNumber	Owner	Created By	CreatedOn
XXXXX0000009	XXX-407213-BJR8JJ	xxxxx xxxxxx	xxxxx xxxxxx	3/10/2009 14:56
XXXXX0000009	XXX-407770-X6B7N9	xxxxx xxxxxx	xxxxx xxxxxx	3/12/2009 14:28
XXXXXA0000CK	XXX-408199-D53LX6	xxxxx xxxxxx	xxxxx xxxxxx	3/13/2009 20:49
XXXXXA0000CK	XXX-408759-2MXQ2H	xxxxx xxxxxx	xxxxx xxxxxx	3/17/2009 19:48
XXXXXA0000L2	XXX-409709-26GKQL	xxxxx xxxxxx	xxxxx xxxxxx	3/20/2009 21:39
XXXXXA0000RS	XXX-407091-45U1QB	xxxxx xxxxxx	xxxxx xxxxxx	3/9/2009 19:57
XXXXXA00010G	XXX-407141-XP3Q4D	xxxxx xxxxxx	xxxxx xxxxxx	3/9/2009 21:57
XXXXXA000157	XXX-409149-KVA2D1	xxxxx xxxxxx	xxxxx xxxxxx	3/19/2009 14:41
XXXXXA000157	XXX-409447-D0G7PA	xxxxx xxxxxx	xxxxx xxxxxx	3/20/2009 14:36
XXXXXA000181	XXX-408539-NXANBP	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 21:50
XXXXXA00018X	XXX-408545-1NLRND	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 21:53
XXXXXA0001VM	XXX-408356-OAONNP	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 16:03
XXXXXA00006O	XXX-409210-YUVLYJ	xxxxx xxxxxx	xxxxx xxxxxx	3/19/2009 16:35
XXXXXA00006O	XXX-409651-I17PSK	xxxxx xxxxxx	xxxxx xxxxxx	3/20/2009 20:13
XXXXXA0000A7	XXX-408548-SOBBR4	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 21:55
XXXXXA0000B5	XXX-407085-75QY6C	xxxxx xxxxxx	xxxxx xxxxxx	3/9/2009 19:50
XXXXXA0000HP	XXX-408760-ATXZUK	xxxxx xxxxxx	xxxxx xxxxxx	3/17/2009 19:50
XXXXXA0000V6	XXX-408549-4EONN5	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 21:58
XXXXXA00017M	XXX-408551-7EY427	xxxxx xxxxxx	xxxxx xxxxxx	3/16/2009 22:01
XXXXXA00000C	XXX-409042-KLBR9Z	xxxxx xxxxxx	xxxxx xxxxxx	3/18/2009 20:21
XXXXXA00004Y	XXX-408942-U6XUQ8	xxxxx xxxxxx	xxxxx xxxxxx	3/18/2009 16:45
XXXXXA00004Y	XXX-408943-J59UGX	xxxxx xxxxxx	xxxxx xxxxxx	3/18/2009 16:45
XXXXXA00006W	XXX-407758-HHT6VL	xxxxx xxxxxx	xxxxx xxxxxx	3/12/2009 14:02
XXXXXA00006W	XXX-409203-N8J4D5	xxxxx xxxxxx	xxxxx xxxxxx	3/19/2009 16:25
XXXXXA0000AZ	XXX-409540-9MYGU2	xxxxx xxxxxx	xxxxx xxxxxx	3/20/2009 17:51
XXXXXA0000G6	XXX-409025-KL1E3Z	xxxxx xxxxxx	xxxxx xxxxxx	3/18/2009 19:47
XXXXXA0000SJ	XXX-408683-NB76N9	xxxxx xxxxxx	xxxxx xxxxxx	3/17/2009 17:49
XXXXXA00001Z	XXX-409542-0DL50V	xxxxx xxxxxx	xxxxx xxxxxx	3/20/2009 17:53

xclr8tr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources