[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Finding duplicate in Tsql

Posted on 2011-10-26
4
Medium Priority
?
245 Views
Last Modified: 2012-05-12
I have the following query built based on some advice I got here earlier.

What I hope to find is a list of all of the records where the same chartnum has duplicated medications. What it seems to bring back is one row per medication and the count tells me how many times that medication is in the table (or how many different chartnums have that med)

Can someone tweak this code to work as I desire?

Thanks.
select * from (
SELECT 
      [chartnum]
      ,[org]
      ,[medication]
      ,startdate
      ,stopdate
      ,entrydate
      ,strength
      ,inactivedate
      ,foreignsysid
      ,quantity
      ,refills
      ,dose
      ,dose_unit
      ,duration
      ,updatedate
     , count(medication) over (partition by medication) counter
      
  FROM [Rx].[dbo].[MedicationList] (nolock)
  where org = 'BNMG') der where der.counter > 1
  order by chartnum, medication

Open in new window

0
Comment
Question by:rutledgj
  • 2
4 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 1000 total points
ID: 37033007
This will return all BNMG records where the combination of  medication and chartnum is listed more than once.  

SELECT medication, chartnum, COUNT(*) AS Total
FROM [Rx].[dbo].[MedicationList] (nolock)
WHERE org = 'BNMG'
GROUP BY medication, chartnum HAVING COUNT(*) > 1

Greg

0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 37033038
If you need all of those columns in the example that you listed, you could do something like this.

Greg


;WITH Duplicates 
AS
(
SELECT org, medication, chartnum, COUNT(*) AS Total
FROM [Rx].[dbo].[MedicationList] (nolock)
WHERE org = 'BNMG'
GROUP BY medication, chartnum HAVING COUNT(*) > 1
)
SELECT 
      a.[chartnum]
      ,a.[org]
      ,a.[medication]
      ,a.startdate
      ,a.stopdate
      ,a.entrydate
      ,a.strength
      ,a.inactivedate
      ,a.foreignsysid
      ,a.quantity
      ,a.refills
      ,a.dose
      ,a.dose_unit
      ,a.duration
      ,a.updatedate
     , b.Total
FROM [Rx].[dbo].[MedicationList] a (nolock) INNER JOIN 
	Duplicates b ON a.org = b.org AND a.medication = b.medication AND a.chartnum = b.chartnum
WHERE a.org = 'BNMG'
ORDER BY a.chartnum, a.medication

Open in new window

0
 
LVL 32

Assisted Solution

by:Ephraim Wangoya
Ephraim Wangoya earned 1000 total points
ID: 37035013
try

1.
SELECT 
      [chartnum]
      ,[org]
      ,[medication]
      ,startdate
      ,stopdate
      ,entrydate
      ,strength
      ,inactivedate
      ,foreignsysid
      ,quantity
      ,refills
      ,dose
      ,dose_unit
      ,duration
      ,updatedate
FROM [Rx].[dbo].[MedicationList] A
inner join 
  (SELECT [chartnum],[medication]      
   FROM [Rx].[dbo].[MedicationList]
   where org = 'BNMG'
   group by [chartnum],[medication]
   having COUNT(*) > 1 
  ) B on B.[chartnum] = A.[chartnum]

Open in new window


2.  
with cte as
(
	SELECT 
      [chartnum]
      ,[org]
      ,[medication]
      ,startdate
      ,stopdate
      ,entrydate
      ,strength
      ,inactivedate
      ,foreignsysid
      ,quantity
      ,refills
      ,dose
      ,dose_unit
      ,duration
      ,updatedate
      ,ROW_NUMBER() over (Partition By [chartnum], [medication] Order By [chartnum], [medication]) rn
	FROM [Rx].[dbo].[MedicationList]
)

select * 
from cte 
where rn > 1

Open in new window

0
 

Author Closing Comment

by:rutledgj
ID: 37037409
both of these appear to work. Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…

834 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