Solved

Oracle: Duplicate data search query

Posted on 2015-02-24
6
207 Views
Last Modified: 2015-02-24
Experts,

I am trying to identify (return records) for records that meet a specific criteria.  For example.  I have a maintenance table that shows the ticket details for each maintenance. For each device that this being worked on during the maintenance will create a record having the same GUI ID.  

What I would like to do is identify for EACH day device names that are showing up more than once.  This will help me de-conflict maintenance before approving them.

Here is the query that I am trying to modify:

SELECT CM.CHANGE_ID,CM.PRIMARY_START_DATE_TIME,DE.DEVICE_NAME
from CHANGE_REQUEST CM INNER JOIN RELATED_DEVICES DE ON CM.GUI_ID=DE.GUI_ID AND DE.RECORD_STATUS = 0
   AND CM.PRIMARY_START_DATE_TIME >= TO_DATE('1/1/2015','MM/DD/YYYY') 
     AND CM.PRIMARY_START_DATE_TIME < TO_DATE('2/1/2015','MM/DD/YYYY') 

Open in new window


See example.  The highlighted rows are the only records I want to return, since  both change id's are on the same day and the device Name match.

Data-example.xlsx

Thank you
0
Comment
Question by:Maliki Hassani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40628779
Untested but try this:

select CHANGE_ID, CHANGE_ID, DEVICE_NAME
from (
SELECT CM.CHANGE_ID,CM.CHANGE_ID,DE.DEVICE_NAME,
count(*) over(partition by CM.PRIMARY_START_DATE_TIME,DE.DEVICE_NAME) row_count
from CHANGE_REQUEST CM INNER JOIN RELATED_DEVICES DE ON CM.GUI_ID=DE.GUI_ID AND DE.RECORD_STATUS = 0
   AND CM.PRIMARY_START_DATE_TIME >= TO_DATE('1/1/2015','MM/DD/YYYY')
     AND CM.PRIMARY_START_DATE_TIME < TO_DATE('2/1/2015','MM/DD/YYYY')
)
where row_count > 1
0
 

Author Comment

by:Maliki Hassani
ID: 40628797
Looks good..  Let me keep this open for further testing.  Thank you
0
 

Author Comment

by:Maliki Hassani
ID: 40628836
I see where it is looking for a match when records have the same exact primary start date time.  How would I change this to show tickets that are within the same date?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40628840
>>How would I change this to show tickets that are within the same date?

...
count(*) over(partition by trunc(CM.PRIMARY_START_DATE_TIME),DE.DEVICE_NAME) row_count
...
0
 

Author Comment

by:Maliki Hassani
ID: 40628841
Brilliant... lol
0
 

Author Closing Comment

by:Maliki Hassani
ID: 40629247
Thank you!
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.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

630 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