• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 174
  • Last Modified:

SQL Query

I have a 2000 SQL server. I am trying to make a query that will report all duplicates in the activity table based on the inventory date and report back the unit number that are duplicates. Does anyone know how to get this done?

Table design:

Unit No            Inventory Date            Service Code

SUDE5176310       2007-07-02 11:45:00            MON
 
0
ipcipher
Asked:
ipcipher
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT UnitNo, CONVERT(varchar(10),InventoryDate, 120) InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, CONVERT(varchar(10),InventoryDate, 120)
0
 
Patrick MatthewsCommented:
Hello ipcipher,

To see just the duplicates:

SELECT UnitNo, CONVERT(varchar(10),InventoryDate, 120) InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, CONVERT(varchar(10),InventoryDate, 120)
HAVING COUNT(*) > 0

Regards,

Patrick
0
 
LowfatspreadCommented:
not points for me please

matthewspatrick

i presume you mean

HAVING COUNT(*) > 1
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Patrick MatthewsCommented:
Lowfatspread said:
>>i presume you mean
>>
>>HAVING COUNT(*) > 1

Yes, of course.  How silly of me :)

Thanks for watching my back, Lowfatspread.
0
 
ipcipherAuthor Commented:
I need it to look at the whole Inventory date field and find duplicates using the inventory date field. It seems like it is only using part of the inventory date field. The inventory date contains data like 2007-07-02 11:45:00. If both the date and time in the inventory field are duplicates then the unit most be reported.

SELECT UnitNo, CONVERT(varchar(10),InventoryDate, 120) InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, CONVERT(varchar(10),InventoryDate, 120)
HAVING COUNT(*) > 0

This is what the output of the script is
GESU9174991      2007-02-05      2
CPSU5143861      2007-01-24      2
HLXU6763280      2007-05-30      2
GESU9150876      2007-01-11      2
HLXU6706064      2006-11-22      2
HLXU6772298      2007-03-19      2
0
 
Patrick MatthewsCommented:
SELECT UnitNo, InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, InventoryDate
HAVING COUNT(*) > 1
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT UnitNo, InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, InventoryDate
HAVING COUNT(*) > 0

0
 
ipcipherAuthor Commented:
What if I also want to add the service code in the search criteria? So that the unit no, inventory date and service code have to match before it is reported. For example


Unit No                  Inventory Date                  Service Code

SUDE5176310       2007-07-02 11:45:00                  MON
SUDE5176310       2007-07-02 11:45:00                  MON

Unit SUDE5176310 would be reported as being duplicate.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT UnitNo, InventoryDate, Count(*) as tot, ServiceCode
FROM urTable
GROUP BY  UnitNo, InventoryDate,ServiceCode
HAVING COUNT(*) > 0
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now