cancel
Showing results for 
Search instead for 
Did you mean: 

Query to find duplicate referance numbers

Former Member
0 Kudos

Dear,

We have somehow managed to create the same sales order twice or more. In the field "Customer Ref. No." we always type in BP's ref. no. So what I need is a query which will show me all open sales orders which have more than one identical "Customer Ref. No." (ORDR.NumAtCard)

So what I got so far is:

SELECT T0.[DocNum], T0.[NumAtCard] FROM ORDR T0 WHERE T0.[DocStatus] <> 'C' AND ????

Hope anyone can help. Thanks.

BR

Kjetil Sandvik

Accepted Solutions (1)

Accepted Solutions (1)

former_member541807
Active Contributor
0 Kudos

hi,

Welcome to the Forum.

try this ....

SELECT     COUNT(T0.NumAtCard) AS 'Count', max(T0.NumAtCard) as 'Customer Ref. No.'
FROM         ORDR  T0
GROUP BY T0.NumAtCard, T0.DocStatus 
HAVING      T0.NumAtCard = '[%0]' AND T0.DocStatus = 'O'

regards,

Fidel

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi Kjetil Sandvik,

If you want to block Sales Order with duplicare Customer Ref. No then

Try this SP.....

if (@object_type = '17')
and (@transaction_type= 'A' or @transaction_type= 'U')
begin

declare @Refno as varchar(100), @Cust as varchar(100) 
if @object_type = '17'
begin

select @Refno = NumAtCard,
@Cust=CardCode
from ordr 
where DocEntry =@list_of_cols_val_tab_del 

if 1 != (select count(DocEntry) from ordr with(nolock) where NumAtCard = @Refno and CardCode=@Cust) 
begin
select @error = 1
select @error_message = 'Customer Ref. No Should Not Be Repeated. ! '
end
end
end

Thanks,

Srujal Patel

jbrotto
Active Contributor
0 Kudos

Try creating an alias and repeat the table select twice and if reference of table 1 = table 2. You could also use a sp to stop transactions like this occurring.

Former Member
0 Kudos

Try this...

SELECT TOP (100) PERCENT DocNum, NumAtCard, COUNT(NumAtCard) AS Entered, DocStatus

FROM dbo.ORDR

GROUP BY DocNum, DocStatus, NumAtCard

HAVING (DocStatus = 'C')

ORDER BY Entered DESC

Regards,

Ben