Oracle: Duplicate data search query

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
Maliki HassaniAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
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
 
Maliki HassaniAuthor Commented:
Looks good..  Let me keep this open for further testing.  Thank you
0
 
Maliki HassaniAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

 
slightwv (䄆 Netminder) Commented:
>>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
 
Maliki HassaniAuthor Commented:
Brilliant... lol
0
 
Maliki HassaniAuthor Commented:
Thank you!
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.