[Koha] Missing barcode number ascending order -SQL query

[Yatheesh lis]

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 at 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 at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha



-- 

Y.Yatheesh Babu