Solved

SQL Query - Group By with Like

Posted on 2013-05-15
10
290 Views
Last Modified: 2013-05-16
Good Day Experts!

I have a situation that I am not sure how to approach.

My query:

Select count([Pro Number]) as myCount From Customer
Group By [Pro Number]
Having count([Pro Number]) > 1

So, I get [Pro Number] values that are duplicates.

I would like to get the following to be considered duplicates:
123456789
CAR123456789

Is this possible?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
10 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
Comment Utility
Are the values always prefixed with CAR if they have anything? Some examples would be helpful. If there is a consistent difference then it is as easy as replacing the CAR with an empty string.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
The challenge is not skipping the character data, that is trivial to code (although probably a dog to run), the problem is actually reporting the original [Pro Number].  However from looking at your query you do not include it so you may not need it.  All you need is a function (could even be a CLR) to lose the character data, so that your query looks like this:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY dbo.udf_GetNumericOnly([Pro Number])
HAVING  COUNT(*) > 1

Open in new window

0
 

Author Comment

by:Jimbo99999
Comment Utility
Thanks for responding.  Yes, the prefix will always be the same like the above example.  I am trying to catch duplicates with this keying error.  I just can't seem to figure out the SQL syntax to add to my query.
0
 
LVL 7

Assisted Solution

by:Ross Turner
Ross Turner earned 100 total points
Comment Utility
I don't know if i missing a trick but couldn't you do this

Create a rank based off ProNumbers without the preceding CAR and Rank them.
If it over 1 it's a duplicate.

http://sqlfiddle.com/#!3/24d1f/22

Select * From 
(
Select 
* ,
Rank() over (partition by replace(ProNumber,'CAR','') order by ProNumber) as Ranked
From 
Customer
) As TEST 
Where Ranked > 1

Open in new window

0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 125 total points
Comment Utility
Yes, the prefix will always be the same like the above example.
Then it should be as simple as:
SELECT  COUNT(*) AS myCount
FROM    Customer
GROUP BY SUBSTRING([Pro Number], 4, 50)  -- Change 50 as appropriate
HAVING  COUNT(*) > 1

Open in new window

0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
Can you provide some sample data and your expected results?
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 250 total points
Comment Utility
Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From dbo.Customer
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1


for example:

Select
    CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number] AS [Pro Number],
    Count([Pro Number]) as myCount
From (
    select '123456789' as [pro number] union all
    select 'CAR123456789'
) AS test_data
Group By CASE WHEN LEFT([Pro Number], 1) <> 'C' THEN 'CAR' ELSE '' END + [Pro Number]
Having count([Pro Number]) > 1
0
 

Author Comment

by:Jimbo99999
Comment Utility
Ok, back online.

I have a little more background for the request I was given.  This stems from duplicate keyed values into the system.  The current report I have written finds "exact matches" on the value.  It has come to light, that a particular situation occured where an alpha value is being typed prior to the numeric value.

So, Keyer1 enters order with invoice 123456.  Then, unknowinlgy Keyer2 enters same order for invoice 123456 but puts CARS in front of it due to a misunderstanding in the training process.

I am tasked with finding all of the values in this scenario.  I have to find the following:

123456
CARS123456

987654
CARS987654

But not all CARSxxxxxx will have a corresponding xxxxxx value.

"Thinking through this I need to:"
1)Find all values with CARS in front
2)Then take the numeric value after the CARS and see if that is a value found in the table
3)If 1) and 2) satisfied, then display both on duplicate report
0
 
LVL 40

Assisted Solution

by:Sharath
Sharath earned 25 total points
Comment Utility
see this.
SELECT * FROM (
SELECT  *,COUNT(*) OVER (PARTITION BY SUBSTRING([Pro Number], 4, 50)) Cnt
FROM    Customer) t1
WHERE cnt > 1

Open in new window

0
 

Author Closing Comment

by:Jimbo99999
Comment Utility
The "little" project has been cancelled.  I appreciate all the responses and have awarded points for your efforts.  In my spare time I will still try to get it working for future reference.

Thanks again,
jimbo99999
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now