?
Solved

SQL Query

Posted on 2007-07-25
9
Medium Priority
?
171 Views
Last Modified: 2010-03-19
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
Comment
Question by:ipcipher
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19565724
SELECT UnitNo, CONVERT(varchar(10),InventoryDate, 120) InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, CONVERT(varchar(10),InventoryDate, 120)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19566004
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 19566416
not points for me please

matthewspatrick

i presume you mean

HAVING COUNT(*) > 1
0
Technology Partners: 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!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 19566495
Lowfatspread said:
>>i presume you mean
>>
>>HAVING COUNT(*) > 1

Yes, of course.  How silly of me :)

Thanks for watching my back, Lowfatspread.
0
 
LVL 1

Author Comment

by:ipcipher
ID: 19566958
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
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 1600 total points
ID: 19566992
SELECT UnitNo, InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, InventoryDate
HAVING COUNT(*) > 1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 19567024
SELECT UnitNo, InventoryDate, Count(*) as tot
FROM urTable
GROUP BY  UnitNo, InventoryDate
HAVING COUNT(*) > 0

0
 
LVL 1

Author Comment

by:ipcipher
ID: 19567807
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
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 400 total points
ID: 19567870
SELECT UnitNo, InventoryDate, Count(*) as tot, ServiceCode
FROM urTable
GROUP BY  UnitNo, InventoryDate,ServiceCode
HAVING COUNT(*) > 0
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

850 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