Attachment 105356 Details for Bug 24868 – The different queries used to count holds vs issues

The different queries used to count holds vs issues

count_difference.txt (text/plain), 4.52 KB, created by Nick Clemens (kidclamp) on 2020-05-26 11:37:24 UTC

(hide)

Description:

Filename:

MIME Type:

Creator: Nick Clemens (kidclamp)

Created: 2020-05-26 11:37:24 UTC

Size: 4.52 KB

patch

obsolete

>HOLDS COUNT:
>Default rule/specific rule - patron's home
>        SELECT count(*) AS count
>          FROM reserves
>     LEFT JOIN items USING (itemnumber)
>     LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
>     LEFT JOIN borrowers USING (borrowernumber)
>         WHERE borrowernumber = ?
>    AND ( borrowers.branchcode = ? OR borrowers.branchcode IS NULL )
>
>Default rule/specific rule - item's home library
>        SELECT count(*) AS count
>          FROM reserves
>     LEFT JOIN items USING (itemnumber)
>     LEFT JOIN biblioitems ON (reserves.biblionumber=biblioitems.biblionumber)
>     LEFT JOIN borrowers USING (borrowernumber)
>         WHERE borrowernumber = ?
>    AND ( items.homebranch = ? OR items.homebranch IS NULL )
>
>ISSUES COUNT:
>Default rule - patron's library
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?
>Specific rule - patron's library
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?
>
>Default rule - item's home library
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?
>Specific rule - item's home library
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?  AND items.homebranch = ?
>
>Default rule - librayr logged in at
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?
>Specific rule - library logged in at
>            SELECT COUNT(*) AS total, COALESCE(SUM(onsite_checkout), 0) AS onsite_checkouts
>            FROM issues
>            JOIN items USING (itemnumber)
>         WHERE items.itype NOT IN (
>                                    SELECT itemtype FROM circulation_rules
>                                    WHERE branchcode = ?
>                                    AND   (categorycode = ? OR categorycode = ?)
>                                    AND   itemtype IS NOT NULL
>                                    AND   rule_name = 'maxissueqty'
>                                  )  AND borrowernumber = ?  AND issues.branchcode = ?
>
>
>
>
>
>
>
>

Actions: View

Attachments on bug 24868: 105356