Not Equal To 1

[Ringers]

Hi All,

I have a invoice's table and am trying to write a script looking for duplicate invoice numbers. The catch is the same invoice number can be used by different suppliers within the same receiving company.

This is what I have so far;

select distinct invoicenumber,count(invoicenumber)
from [xxx].[dbo].[invoice_head]
group by invoicenumber having count(invoicenumber) > 1

sample return;

ID date supplier invoiceid companyid amount
2221 12/2/11 jones pty 0002 41 $250
3212 13/4/11 acme pty 0002 41 $110
5454 01/01/11 maccas 0002 41 $500

I was thinking of trying a 'where' it is not equal to itself(the company id is not the same) so it would return the count only if the company is is different.

But not sure what to do next, maybe join the supplier to the supplier table?

Thanks

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT invoicenumber
            FROM YourTable
            GROUP BY invoicenumber
            HAVING VOUNT(*) > 1) Tbl1
ON YourTable.invoicenumber = Tbl1.invoicenumber

NOT TESTED!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Ringers]

Hey bborissov

Thanks for the code, can you explain the 'tb11' and the 'on' statement?

 

[bborissov]

Tbl1 is so called derived table and ON is the JOIN condition.
BTW I'm not sure if I'm understanding you fully, could you please post some example data and desired result?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Ringers]

unfortunaley it doesn't work;

SELECT distinct dbo.invoice_head.InvoiceNumber
from [xxx].[dbo].[invoice_head]
INNER JOIN (SELECT InvoiceNumber FROM dbo.invoice_head GROUP BY InvoiceNumber HAVING COUNT(*) > 1) Tbl1
ON invoice_head.invoicenumber = Tbl1.invoicenumber

it returns rows ok, but when i check the invoice number with a select * where equals to a returned number, the duplicate invoices have the same company id. it needs to shows the same invoicenumber where the company is not equal.

 

[bborissov]

OK, give me some example data and desired result from it.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[drlex]

Wouldn't grouping by supplier solve the issue?

Amending bborissov's code:

SELECT #YourTable.*
FROM #YourTable
INNER JOIN (SELECT supplier, invoiceid
            FROM #YourTable
            GROUP BY supplier, invoiceid
            HAVING COUNT(*) > 1) Tbl1
ON #YourTable.invoiceid = Tbl1.invoiceid and #YourTable.supplier = Tbl1.supplier

Test:

create table #yourtable (ID int, idate datetime, supplier varchar(16), invoiceid varchar(4), 
[companyid] int, amount float)

Insert into #yourtable
select
2221, '2011-02-12','jones pty', 0002,41,250
union select
3212, '2011-04-13','acme pty',  0002,41,110
union select
5454, '2011-01-01','maccas',    0002,41,500
union select
5455, '2011-03-01','maccas',    0002,41,600

SELECT #YourTable.*
FROM #YourTable
INNER JOIN (SELECT supplier, invoiceid
            FROM #YourTable
            GROUP BY supplier, invoiceid
            HAVING COUNT(*) > 1) Tbl1
ON #YourTable.invoiceid = Tbl1.invoiceid and #YourTable.supplier = Tbl1.supplier

drop table #yourtable

soi là, soi carré

 

[Ringers]

Think my logic was wrong.

I need all invoices from the table invoice_Head where the invoicenumber(nvchar(100)) and company_id(int) of the invoices with the same number is the same.

Meaning all invoices with the same number with the same company id.

Here is a sample of duplicate invoices i found by doing a select all from the invoice_head table.

InvoiceId,IssueDate,ScanDate,Supplier,InvoiceNumber,InvoiceTotal, company id
100636 2010-11-29 2010-12-30 STELLAR SOUND SDN BHD 145 12500 28

98996 2010-11-29 2010-12-20 STELLAR SOUND-STUDIOS 145 12500 28

You can see the invoicenumber is the same, the amount is the same and the company id is the same. so this is a duplicate invoice. Ignore the slight difference in the supplier just bad data.

 

[drlex]

Amending bborissov's code:

SELECT invoice_Head.*
FROM invoice_Head
INNER JOIN (SELECT company_id, InvoiceNumber
            FROM #YourTable
            GROUP BY company_id, InvoiceNumber
            HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id

soi là, soi carré

 

[Ringers]

Many Thanks for code update drlex.

I need one more modification to it. I have found that I need to compare supplier name and make sure there same. Becuase with this code I am getting the same invoice code with the same company, which is correct. But the company name are different, so i have tried changing and it doesn't work.

So if you are able to point me in the right direction.

SELECT *
FROM invoice_Head,dbo.U_Suppliers
INNER JOIN (SELECT company_id,InvoiceNumber, supplier
FROM invoice_head
GROUP BY company_id,InvoiceNumber
HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id
and U_Suppliers.SupplierName1 = TBl1.Supplier

 

[drlex]

If the company name is important in determining the invoice count, then you'll need to group on Supplier in the sub-query.

soi là, soi carré

 

[Ringers]

Sorry still doesn't work;

SELECT invoice_head.*
FROM invoice_Head,dbo.U_Suppliers
INNER JOIN (SELECT company_id,InvoiceNumber, supplier
FROM invoice_head
GROUP BY company_id,InvoiceNumber,supplier HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id and U_Suppliers.SupplierName1 = TBl1.Supplier

Errors:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "invoice_Head.InvoiceNumber" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "invoice_Head.company_id" could not be bound.

 

[Ringers]

this is my last attempt for the day;

SELECT invoice_head.*
FROM invoice_Head, U_Suppliers
INNER JOIN (SELECT company_id,InvoiceNumber,supplier
FROM invoice_head
GROUP BY company_id,InvoiceNumber,supplier HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id and Invoice_head.Supplier = U_Suppliers.SupplierName1

 

[drlex]

The issue is with your join syntax.
If you are joining 3 tables with JOIN, then you need to specify the joins between each table separately.

Example:

SELECT * FROM tablea 
INNER JOIN tableb 
 ON tablea.data = tableb.data
INNER JOIN tablec 
 ON tableb.data2 = tablec.data2

As written, the SQL Parser is 'instructed' to link U_Suppliers to Tbl1, but 'given' join details for invoice_Head and Tbl1.



soi là, soi carré

 

[Ringers]

I have this now;

SELECT invoice_Head.*
FROM invoice_Head
INNER JOIN (SELECT company_id, InvoiceNumber
FROM invoice_head
GROUP BY company_id, InvoiceNumber HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id
INNER JOIN U_Suppliers
ON u_suppliers.SupplierName1 =invoice_head.Supplier

While it complies and runs, the returned invoices are not the duplicate one. I don't care if the last join is on the supplier name or the invoice total. Can you show me correct code, thanks.

 

[Ringers]

it 's not three tables but three fields in the same table. Company Id, InvoiceNumber and Supplier or Invoice total. The invoice number where these fields are the same.

 

[drlex]

You need to group on supplier if you're wanting to find duplicates which depend to such.
Try this:

SELECT invoice_Head.*
FROM invoice_Head
INNER JOIN (SELECT company_id, [b]supplier,[/b] InvoiceNumber
             FROM invoice_head            
              GROUP BY company_id, [b]supplier,[/b] InvoiceNumber HAVING COUNT(*) > 1) Tbl1
ON invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and invoice_Head.company_id = Tbl1.company_id
[b]and invoice_Head.supplier = Tbl1.supplier[/b]
INNER JOIN U_Suppliers
ON u_suppliers.SupplierName1 =invoice_head.Supplier

soi là, soi carré

 

[drlex]

(Test data used:

create table #invoice_Head (company_ID int, idate datetime, supplier varchar(16), invoiceNumber  int, 
[companyid] int, amount float)

Insert into #invoice_Head
select
2221, '2011-02-12','jones pty', 0002,41,250
union select
3212, '2011-04-13','acme pty',  0002,41,110
union select
5454, '2011-01-01','maccas',    0002,41,500
union select
5455, '2011-03-01','maccas',    0002,41,600
union select
5455, '2011-05-01','maccas',    0002,41,700

create table #U_suppliers (SupplierName1 varchar(16), SupplierFullName varchar(40)) 
Insert into #U_suppliers
select
'jones pty', 'Jones the Steam plc'
union select
'acme pty',  'Acme explosives & rocket parts corp'
union select
'maccas', 'Big Maccas fastfood ltd'


SELECT #invoice_Head.*, #U_suppliers.SupplierFullName
FROM #invoice_Head
INNER JOIN (SELECT company_id, supplier, InvoiceNumber
             FROM #invoice_head            
              GROUP BY company_id, supplier, InvoiceNumber HAVING COUNT(*) > 1) Tbl1
ON #invoice_Head.InvoiceNumber = Tbl1.InvoiceNumber and #invoice_Head.company_id = Tbl1.company_id
and #invoice_Head.Supplier = Tbl1.Supplier
INNER JOIN #U_Suppliers
ON #u_suppliers.SupplierName1 =#invoice_head.Supplier

drop table #invoice_Head
drop table #u_Suppliers

results:

company_ID  idate                   supplier         invoiceNumber companyid   amount                 SupplierFullName
----------- ----------------------- ---------------- ------------- ----------- ---------------------- ----------------------------------------
5455        2011-03-01 00:00:00.000 maccas           2             41          600                    Big Maccas fastfood ltd
5455        2011-05-01 00:00:00.000 maccas           2             41          700                    Big Maccas fastfood ltd

(2 row(s) affected)

soi là, soi carré

 

[Ringers]

Thanks heaps drlex very helpful.

 

[drlex]

You're welcome; glad it helped and thank you for the accolade.

soi là, soi carré