SQL Duplicates

I need to find all records in a given table that have multiple services on the same day.

Sample Data:
ID      Name            Date of Service            Service Code
100      John Doe      2/12/2015            30
100      John Doe      2/12/2015            30
100      John Doe      2/12/2015            40
103      Jane Doe              3/14/2015            20
103      Jane Doe              3/18/2015            50
117      Mickey Mouse      3/20/2015            10
117      Mickey Mouse      3/20/2015            12

Using the data above, I need IDs 100 and 117 returned along with other fields about their service from this table.

The good news is that all of the data I'm needing is from the same table (that never happens!), but I'm needing the data returned, not just counts of the duplicated records, so I'm not sure how to do that.

Any ideas?
fcsITAsked:
Who is Participating?
 
arnoldCommented:
select id,name, date of service, count(date of service) from tablename  where count(date of service) > 1 group by id, date of service, name  

you can do a select to get the ID where there are multiple services, and then query based on that.
I've not master that yet, cts

select id, name, date of service, service code from table where iD in (select ID from table where count (id) >1 group by id, service date)
0
 
arnoldCommented:
An example of the combination can be gleaned from the question http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28601991.html#a40565945

Here is the MS explanation of the mechanism
https://msdn.microsoft.com/en-us/library/ms175972.aspx
0
 
premkumar somasundaram palaniCommented:
Assuming to find the duplicates within group of ID

select * from table_A t1,
(select id,date_of_service from table_A group by id,date_of_service having count(*) > 1) tmp
where t1.id=tmp.id
and t1.date_of_service=tmp.date_of_service
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Jeff DarlingDeveloper AnalystCommented:
If you like nesting

CREATE TABLE #Employees 
( 
    id INT, 
    name varchar(30),
    dateofservice Datetime,
    servicecode int
)

INSERT #Employees(id,name,dateofservice,servicecode) VALUES(100,'John Doe','2/12/2015',30) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(100,'John Doe','2/12/2015',30) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(100,'John Doe','2/12/2015',40) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(103,'Jane Doe','3/14/2015',20) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(103,'Jane Doe','3/18/2015',50) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(117,'Mickey Mouse','3/20/2015',10) 
INSERT #Employees(id,name,dateofservice,servicecode) VALUES(117,'Mickey Mouse','3/20/2015',12) 

SELECT *
FROM #Employees
WHERE id IN (
		SELECT id
		FROM (
			SELECT ID
				,count(dateofservice) AS cnt
			FROM #Employees
			GROUP BY id
				,dateofservice
			) G
		WHERE cnt > 1
		)

Open in new window

0
 
arnoldCommented:
Do you want the data
id, name, service date, 30,40,12,20,50,10
100,john doe, 2/12/2015,2,1,0,0,0,0
103,jane,date,0,0,0,1,0,0
103,jane,second,0,0,0,0,1,0
117,mickey mouse, 3/20/2015,0,0,1,0,0,1
0
 
fcsITAuthor Commented:
Thanks everyone.

arnold, yes.  I need the data.  I haven't gotten it to work yet.  I'm trying everyone's suggestions.
0
 
PortletPaulfreelancerCommented:
I wasn't sure what the expected result is, I assume it's like this?
|  ID |         Name |            Date of Service | Service Code | countof |
|-----|--------------|----------------------------|--------------|---------|
| 100 |     John Doe | February, 12 2015 00:00:00 |           30 |       3 |
| 100 |     John Doe | February, 12 2015 00:00:00 |           30 |       3 |
| 100 |     John Doe | February, 12 2015 00:00:00 |           40 |       3 |
| 117 | Mickey Mouse |    March, 20 2015 00:00:00 |           10 |       2 |
| 117 | Mickey Mouse |    March, 20 2015 00:00:00 |           12 |       2 |

Open in new window


from this query:
select
    [ID], [Name], [Date of Service], [Service Code], countof
from (
    select
         [ID], [Name], [Date of Service], [Service Code]
        , count(*) over(partition by ID, [Date of Service]) as countof
    from sample
    ) derived
where countof > 1
;

Open in new window


I've used count(*) over(partition by ID, [Date of Service]) which provides the needed count on each row, then just filter where that is greater then 1

Note, if that date field has times after 00:00:000 a=on any date then you may need:

count(*) over(partition by ID, CAST([Date of Service] AS DATE) )

see: http://sqlfiddle.com/#!6/77518/4
details:
    CREATE TABLE Sample
        ([ID] int, [Name] varchar(12), [Date of Service] datetime, [Service Code] int)
    ;
        
    INSERT INTO Sample
        ([ID], [Name], [Date of Service], [Service Code])
    VALUES
        (100, 'John Doe', '2015-02-12 00:00:00', 30),
        (100, 'John Doe', '2015-02-12 00:00:00', 30),
        (100, 'John Doe', '2015-02-12 00:00:00', 40),
        (103, 'Jane Doe', '2015-03-14 00:00:00', 20),
        (103, 'Jane Doe', '2015-03-18 00:00:00', 50),
        (117, 'Mickey Mouse', '2015-03-20 00:00:00', 10),
        (117, 'Mickey Mouse', '2015-03-20 00:00:00', 12)
    ;
    
**Query 1**:

    select
        [ID], [Name], [Date of Service], [Service Code], countof
    from (
        select
             [ID], [Name], [Date of Service], [Service Code]
            , count(*) over(partition by ID, [Date of Service]) as countof
        from sample
        ) derived
    where countof > 1
    

**[Results][2]**:
    |  ID |         Name |            Date of Service | Service Code | countof |
    |-----|--------------|----------------------------|--------------|---------|
    | 100 |     John Doe | February, 12 2015 00:00:00 |           30 |       3 |
    | 100 |     John Doe | February, 12 2015 00:00:00 |           30 |       3 |
    | 100 |     John Doe | February, 12 2015 00:00:00 |           40 |       3 |
    | 117 | Mickey Mouse |    March, 20 2015 00:00:00 |           10 |       2 |
    | 117 | Mickey Mouse |    March, 20 2015 00:00:00 |           12 |       2 |

  

Open in new window

0
 
Jeff DarlingDeveloper AnalystCommented:
using the sample temp table from my post above...

If you want the counts for the number of dupes

SELECT *
FROM #Employees e
JOIN (
	SELECT id
		,cnt
	FROM (
		SELECT ID
			,count(dateofservice) AS cnt
		FROM #Employees
		GROUP BY id
			,dateofservice
		) G
	WHERE cnt > 1
	) g ON g.id = e.id

Open in new window

0
 
fcsITAuthor Commented:
I wound up going a whole different route, but I will split the points between all four of you for your quick assistance.  I appreciate that!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.