Duplicate data - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Duplicate data

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-10 : 11:47:50

Hi i've a database table were i need to look for repeat orders, the way i get repeat orders is by looking for duplicate Serial numbers and then Duplicate Material Numbers. my problem is that my Order number can also be duplicate so its not unique
Am doing the query in access so i can get the user to enter in a from and to date and the number of days between the two dates.

The issues am having is duplicate row's, as am doing the join on Serial number i need the join as i need the user to enter in 2 date. From date , to Date.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-10 : 12:44:29

Help us help you...

http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-10 : 13:38:13

Something like this?

SELECT   SerialNumber,
         MaterialNumber
FROM     OrderTable
WHERE    OrderDate BETWEEN @FromDate AND @ToDate
GROUP BY SerialNumber,
         MaterialNumber
HAVING   COUNT(*) > 1

Peter Larsson
Helsingborg, Sweden

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-07-11 : 03:45:42

Here is my Query in ACCESS

SELECT Repeat_Orders_Belgium.[Order Create Date], DateDiff("d",[Repeat_Orders_Belgium].[Order Create Date],[Repeat_Orders_Belgium_1].[Order Create Date]) AS [No Of Days], Repeat_Orders_Belgium.[Serial Number], Repeat_Orders_Belgium.[Material No], Repeat_Orders_Belgium.[Sales Doc No]
FROM Repeat_Orders_Belgium AS Repeat_Orders_Belgium_1 INNER JOIN Repeat_Orders_Belgium ON Repeat_Orders_Belgium_1.ID = Repeat_Orders_Belgium.ID
WHERE (((Repeat_Orders_Belgium.[Order Create Date]) Between [Forms]![Belgium_Prompt]![cboStartDate] And [Forms]![Belgium_Prompt]![cboEndDate]) AND ((DateDiff("d",[Repeat_Orders_Belgium].[Order Create Date],[Repeat_Orders_Belgium_1].[Order Create Date]))=[Forms]![Belgium_Prompt]![No Of Days]));

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-11 : 07:09:03

Try posting on this forum instead http://www.sqlteam.com/forums/forum.asp?FORUM_ID=3

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-07-11 : 13:51:01

Put a DISTINCT after SELECT.

SELECT DISTINCT Repeat_Orders_Belgium........

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources