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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
PortletPaulEE Topic AdvisorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.