asked on

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

Aneesh

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

Patrick Matthews

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

Lowfatspread

not points for me please

matthewspatrick

i presume you mean

HAVING COUNT(*) > 1

Patrick Matthews

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

Yes, of course. How silly of me :)

Thanks for watching my back, Lowfatspread.

ipcipher

ASKER

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

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Aneesh

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

ipcipher

ASKER

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.

SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial