0

I'm trying to write some SQL to allow me to get result of possible duplicated Invoices that will have the same [same Items, with Same Quantity], That is possible to be Duplicate issued

  • Invoice items Average around 300 Item

  • Total Invoice To be Revision around 2500 Invoice

The Following is a invoices sample with only 1 items or so, but in real population items average is 300


Inv_ID Item_Code Item_Q
A-800   101010  24
A-801   101010  24
A-802   202020  9
A-803   101010  18
A-804   202020  9
A-805   202020  9
A-806   101010  18

Hoping The Excepted Result will be

A-800, A-801
A-802, A-804, A-805
A-803, A-806

But the invoice has around 200 item, and the duplicated invoices has to be has the same items and exact same quantity for these.

 It's SQL_Server

And The Result need to match the whole Invoices item Like Invoice A has 300 Different Items line with each one Quantity 2 The Results need to be all invoice has the exact same 300 Item with the Exact Quantity.

The Supplier has issued multiple duplicated invoice to our accounting Department by mistakes over 4 years, it was discovered by chance, so we need to find out the duplicated invoice to remove it from payment schedule.

The issued invoices Need to has the exact different items with exact quantity to be considered duplicated.,,,

Community
  • 1
  • 1
Mahmoud Nasr
  • 1
  • 2

1 Answers1

0

You didn't specify your DBMS product, so this answer is for Postgres.

select string_agg(inv_id, ',' order by inv_id) as inv_ids
from the_table
group by item_code, item_q
order by inv_ids;

Online example

Other DBMS products have similar functions to do the aggregation. In Oracle or SQL Server, you would use listagg(), in MySQL you would use group_concat(). The individual syntax would be slightly different (you have to check the manual), but the idea is the same.

  • Thank you For your Help,, when i tested it, the result was each line duplicated result only. The Required query Matching is more complex a little , Result Needed here will be illustrated again as :: **The Result need to match the whole Invoices items ... as Invoice "A-800" has 300 Different Items line with each Item Quantity is "2" ... The Results need to be all invoice has the exact same 300 Item with the Exact Quantity.** That Mean it has to compare each invoice item against the other invoice line by line, and whole invoices count here is around 2500 invoices – Mahmoud Nasr Jun 12 '19 at 04:36
  • The Supplier has issued multiple duplicated invoice to our accounting Department by mistakes over 4 years, it was discovered by chance, so we need to find out the duplicated invoice to remove it from payment schedule. **The issued invoice Need to has the exact different items with exact quantity to be considered duplicated.,,,** – Mahmoud Nasr Jun 12 '19 at 04:42