the SQL query Missing barcode in a range Developer: Josef Moravec Module: Catalog Purpose: If you want to fill the gaps in your barcodes row for items Status: Complet 1) SELECT (i1.barcode + 1) AS gap_starts_at, (SELECT MIN(i3.barcode) -1 FROM items i3 WHERE i3.barcode > i1.barcode) AS gap_ends_at FROM items i1 # Range TO CHECK: WHERE i1.barcode BETWEEN 0 AND 10000 AND NOT EXISTS (SELECT i2.barcode FROM items i2 WHERE i2.barcode = i1.barcode + 1) HAVING gap_ends_at IS NOT NULL 2) Find unused sequential barcode ranges Developer: Jared Camins-Esakov Module: Catalog Purpose: Find ranges of unused barcodes. Status: Completed Note: This query takes a *long* time. Minutes, not seconds. This query will only work on non-checksummed, sequential numeric barcodes ELECT Convert(l.barcode, UNSIGNED) + 1 AS start, MIN(Convert(fr.barcode, UNSIGNED)) - 1 AS stop FROM items AS l LEFT OUTER JOIN items AS r ON Convert(l.barcode, UNSIGNED) = Convert(r.barcode, UNSIGNED) - 1 LEFT OUTER JOIN items AS fr ON Convert(l.barcode, UNSIGNED) < Convert(fr.barcode, UNSIGNED) WHERE r.barcode IS NULL AND fr.barcode IS NOT NULL GROUP BY l.barcode, r.barcode ORDER BY l.barcode I am using koha 19.05. I want to know missing barcode number / unused barcode number in ascending order On Wed, Mar 11, 2020 at 6:36 PM Owen Leonard <oleonard@myacpl.org> wrote:

...

Can anyone help me to get missing barcode number -SQL query.

Can you please explain in more detail what you're trying to do? Which query in the SQL library did you try, and how did it fall short?

-- Owen

-- Web Developer Athens County Public Libraries (740) 737-6006 https://www.myacpl.org _______________________________________________

Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

-- Y.Yatheesh Babu