[Koha] [KOHA] SQL CIRCULATION REPORT

[rogan.hamby at gmail.com]

Hi James,

One simple way to handle it is with a case statement.  The
following example has a static between filter for the dates but it
illustrates this approach. You can also do some cleaning to make sure there
aren't spaces in front and it assumes that all of your DDC numbers in fact
start with a digit.

SELECT
COUNT(*),
CASE
    WHEN LEFT(it.itemcallnumber,1) = '0' THEN '000s'
    WHEN LEFT(it.itemcallnumber,1) = '1' THEN '100s'
    WHEN LEFT(it.itemcallnumber,1) = '2' THEN '200s'
    WHEN LEFT(it.itemcallnumber,1) = '3' THEN '300s'
    WHEN LEFT(it.itemcallnumber,1) = '4' THEN '400s'
    WHEN LEFT(it.itemcallnumber,1) = '5' THEN '500s'
    WHEN LEFT(it.itemcallnumber,1) = '6' THEN '600s'
    WHEN LEFT(it.itemcallnumber,1) = '7' THEN '700s'
    WHEN LEFT(it.itemcallnumber,1) = '8' THEN '800s'
    WHEN LEFT(it.itemcallnumber,1) = '9' THEN '900s'
    ELSE 'OTHER'
    END
from (SELECT * FROM issues UNION ALL SELECT * FROM old_issues) i
JOIN items it ON it.itemnumber = i.itemnumber
WHERE DATE(i.timestamp) BETWEEN '2020-02-01' AND '2020-02-29'
GROUP BY 2 ORDER BY 2;



On Thu, Mar 5, 2020 at 11:06 AM muiru james <muirunyeri at gmail.com> wrote:

> Dear All,
>
> My library uses DDC scheme of classification and we are looking for a
> report that will count items checked out allowing us to select day(s) of
> issue, select itemtype and group the statistics by DDC 10 broad classes.
>
> *EXAMPLE*
>
> IN a date range say 1st-31st March 2020.
>
> *DDC Class  | NO. of books issued*
> 000           -               12
> 100           -               26
> 200           -               157
> .
> .
> .
> 900          -               230
>
> The report can generate statistics for a day, month, quarter, e.t.c.
>
> Any assistance however close will be greatly appreciated.
>
> Thank you as you prepare to help
>
> James
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> Koha at lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>