Re: [Koha] How to add authorized value to a report output

[rogan . hamby]

Hi Joelle,

For the linking between borrower and borrower attributes you want to say
which borrower attribute you want via the code and you may want to specify
the value like this:

 select b.cardnumber from borrowers b join borrower_attributes ba on
ba.borrowernumber = b.borrowernumber where ba.code = 'BANNERHOLD' and
ba.attribute = 'YES';



On Fri, Mar 6, 2020 at 10:12 AM Joelle Hannert  wrote:

> I'm trying to get a report to include an output of a custom borrower
> attribute, but having trouble coming up with the correct way to ask for
> this in the SQL code. I feel like the answer is somewhere on this schema
> page:
> http://schema.koha-community.org/18_11/tables/borrower_attributes.html
> but
> nothing I'm trying is working. The authorized value for our local attribute
> is "BANNERHOLD."
> I tried the following:
>
>- borrower_attributes.bannerhold
>- borrower_attribute_types.bannerhold
>- code.bannerhold
>
> I don't really understand how the relationships work, so it's just trial
> and error for me. Any advice would be much appreciated!
>
> Joelle
>
> --
> Joelle Hannert
> Library Technical Services Coordinator
> Northwestern Michigan College
> (231) 995-1684
> Work days: M, W, Th, F
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] [KOHA] SQL CIRCULATION REPORT

[rogan . hamby]

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

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

Re: [Koha] How to add authorized value to a report output

[rogan . hamby]

You can just select the value then, take out the "and ba.attribute = 'YES'"
and add ba.attribute to your select statement.

On Fri, Mar 6, 2020 at 12:01 PM Joelle Hannert  wrote:

> Sorry, I should have been more clear. I'm not trying to select on the
> BANNERHOLD status (YES/NO/blank), I just want the contents of that field to
> be included in the report output for each patron reported.
>
> On Fri, Mar 6, 2020 at 10:29 AM  wrote:
>
>> Hi Joelle,
>>
>> For the linking between borrower and borrower attributes you want to say
>> which borrower attribute you want via the code and you may want to specify
>> the value like this:
>>
>>  select b.cardnumber from borrowers b join borrower_attributes ba on
>> ba.borrowernumber = b.borrowernumber where ba.code = 'BANNERHOLD' and
>> ba.attribute = 'YES';
>>
>>
>>
>> On Fri, Mar 6, 2020 at 10:12 AM Joelle Hannert  wrote:
>>
>>> I'm trying to get a report to include an output of a custom borrower
>>> attribute, but having trouble coming up with the correct way to ask for
>>> this in the SQL code. I feel like the answer is somewhere on this schema
>>> page:
>>> http://schema.koha-community.org/18_11/tables/borrower_attributes.html
>>> but
>>> nothing I'm trying is working. The authorized value for our local
>>> attribute
>>> is "BANNERHOLD."
>>> I tried the following:
>>>
>>>- borrower_attributes.bannerhold
>>>- borrower_attribute_types.bannerhold
>>>- code.bannerhold
>>>
>>> I don't really understand how the relationships work, so it's just trial
>>> and error for me. Any advice would be much appreciated!
>>>
>>> Joelle
>>>
>>> --
>>> Joelle Hannert
>>> Library Technical Services Coordinator
>>> Northwestern Michigan College
>>> (231) 995-1684
>>> Work days: M, W, Th, F
>>> ___
>>>
>>> Koha mailing list  http://koha-community.org
>>> Koha@lists.katipo.co.nz
>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
>
> --
> Joelle Hannert
> Library Technical Services Coordinator
> Northwestern Michigan College
> (231) 995-1684
> Work days: M, W, Th, F
>
___

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

Re: [Koha] [KOHA] SQL CIRCULATION REPORT- how to SUM

[rogan . hamby]

Hi James,

If I'm understanding correctly you want an additional row giving a sum of
all the numbers in addition to the per class one?  The simplest way  it
occurs to me to do that is with a UNION ALL statement.  I've taken some
filters out for brevity but this illustrates the approach:

SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
count(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON
(statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')
GROUP BY SUBSTRING(itemcallnumber,1,1)
UNION ALL
SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
FROM borrowers
  LEFT JOIN statistics ON
(statistics.borrowernumber=borrowers.borrowernumber)
  LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
  LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
items.biblioitemnumber)
WHERE statistics.type IN ('issue', 'renew')

Basically with UNION ALL you can create other queries that don't have to be
tied to the data of the previous queries so long as the columns line up.



On Thu, Mar 12, 2020 at 3:04 AM muiru james  wrote:

> Hi Rogan and Team,
>
> I really appreciate your assistance towards my problem. The query does
> indeed work to answer my need. Another query was also pushed to me towards
> the same.
>
> I'm now looking for a way to SUM my answer to get the total number of
> books issued. Any suggestions would be most welcome for any of the 2
> queries. The other SQL query is as below: -
>
> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
> count(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
>   AND date(statistics.datetime) BETWEEN <>
> AND <>
>   AND statistics.itemtype = 'BK'
> GROUP BY SUBSTRING(itemcallnumber,1,1)
> ORDER BY items.itemcallnumber ASC
>
> N.B My desire is to SUM the NUMBER column as TOTAL.
>
> Please assist team
>
> Warm Regards.
> JAMES
>
> On Fri, Mar 6, 2020 at 7:10 PM  wrote:
>
>> 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  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@lists.katipo.co.nz
>>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>>
>>
___

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

Re: [Koha] [KOHA] SQL CIRCULATION REPORT- how to SUM

[rogan . hamby]

Hi James,

The query I posted worked with my quick test.  Could you post your query as
it is now?



On Sat, Mar 14, 2020 at 9:52 AM muiru james  wrote:

> Hi Rogan and All,
>
> I cant thank you enough for the tremendous effort you have put towards
> helping me solve my problem.
>
> You do indeed understand well what I need.
>
> The UNION ALL is working well by adding a new row below the last class
> group. However the SUM function is returning a 0 (zero). It seems its not
> adding up the numbers.
>
> Please dont give up but help me find out the last remaining bit. We are
> almost there.
>
> Most appreciated
>
> James
>
>
>
>
>
> On Thu, Mar 12, 2020 at 2:57 PM  wrote:
>
>> Hi James,
>>
>> If I'm understanding correctly you want an additional row giving a sum of
>> all the numbers in addition to the per class one?  The simplest way  it
>> occurs to me to do that is with a UNION ALL statement.  I've taken some
>> filters out for brevity but this illustrates the approach:
>>
>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>> count(statistics.type) AS 'NUMBER'
>> FROM borrowers
>>   LEFT JOIN statistics ON
>> (statistics.borrowernumber=borrowers.borrowernumber)
>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>> items.biblioitemnumber)
>> WHERE statistics.type IN ('issue', 'renew')
>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>> UNION ALL
>> SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
>> FROM borrowers
>>   LEFT JOIN statistics ON
>> (statistics.borrowernumber=borrowers.borrowernumber)
>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>> items.biblioitemnumber)
>> WHERE statistics.type IN ('issue', 'renew')
>>
>> Basically with UNION ALL you can create other queries that don't have to
>> be tied to the data of the previous queries so long as the columns line
>> up.
>>
>>
>>
>> On Thu, Mar 12, 2020 at 3:04 AM muiru james  wrote:
>>
>>> Hi Rogan and Team,
>>>
>>> I really appreciate your assistance towards my problem. The query does
>>> indeed work to answer my need. Another query was also pushed to me towards
>>> the same.
>>>
>>> I'm now looking for a way to SUM my answer to get the total number of
>>> books issued. Any suggestions would be most welcome for any of the 2
>>> queries. The other SQL query is as below: -
>>>
>>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>>> count(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>>   AND date(statistics.datetime) BETWEEN <>> (-mm-dd)|date>> AND <>
>>>   AND statistics.itemtype = 'BK'
>>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>>> ORDER BY items.itemcallnumber ASC
>>>
>>> N.B My desire is to SUM the NUMBER column as TOTAL.
>>>
>>> Please assist team
>>>
>>> Warm Regards.
>>> JAMES
>>>
>>> On Fri, Mar 6, 2020 at 7:10 PM  wrote:
>>>
 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 
 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   -   2

Re: [Koha] [KOHA] SQL CIRCULATION REPORT- how to SUM

[rogan . hamby]

Hi James,

A few thoughts from skimming it.

1) I don't see where you're using biblioitems anywhere in the report unless
I'm just not seeing it so you probably don't need it for a join.  I don't
think it'll hurt anything other than slow the report less than  you'll even
notice but still ... if not needed I'd cut it out.

2) you're going to want the same filters in the second query as the first
to make sure you get the same numbers

3) you are using a SUM on the statistics type but you can only sum numbers
you probably want a COUNT there, which you do in the first query but not
the second

On Sun, Mar 15, 2020 at 12:38 AM muirunyeri  wrote:

> Hi Rogan,
>
> I reintroduced two filters date abd itemtype. So that i can sekect any
> date be it weekly monthly or quarterly etc. I also have different itemtypes
> sone of which do not start wih a digit and I need to filter them out yo get
> accurate results. (Your advice on whether the filters might be sn issue is
> verywelcome)
>
> Below is my query.
>
>
> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
> count(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
> AND date(datetime) BETWEEN <> AND < (-mm-dd)|date>>
> AND items.itype = <>
> GROUP BY SUBSTRING(itemcallnumber,1,1)
> UNION ALL
> SELECT "TOTAL" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
> FROM borrowers
>   LEFT JOIN statistics ON
> (statistics.borrowernumber=borrowers.borrowernumber)
>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
> items.biblioitemnumber)
> WHERE statistics.type IN ('issue', 'renew')
>
>
> Sent from my Samsung Galaxy smartphone.
>  Original message 
> From: rogan.ha...@gmail.com
> Date: 15/03/2020 00:48 (GMT+03:00)
> To: muiru james , Koha 
> Subject: Re: [KOHA] SQL CIRCULATION REPORT- how to SUM
>
> Hi James,
>
> The query I posted worked with my quick test.  Could you post your query
> as it is now?
>
>
>
> On Sat, Mar 14, 2020 at 9:52 AM muiru james  wrote:
>
>> Hi Rogan and All,
>>
>> I cant thank you enough for the tremendous effort you have put towards
>> helping me solve my problem.
>>
>> You do indeed understand well what I need.
>>
>> The UNION ALL is working well by adding a new row below the last class
>> group. However the SUM function is returning a 0 (zero). It seems its not
>> adding up the numbers.
>>
>> Please dont give up but help me find out the last remaining bit. We are
>> almost there.
>>
>> Most appreciated
>>
>> James
>>
>>
>>
>>
>>
>> On Thu, Mar 12, 2020 at 2:57 PM  wrote:
>>
>>> Hi James,
>>>
>>> If I'm understanding correctly you want an additional row giving a sum
>>> of all the numbers in addition to the per class one?  The simplest way  it
>>> occurs to me to do that is with a UNION ALL statement.  I've taken some
>>> filters out for brevity but this illustrates the approach:
>>>
>>> SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
>>> count(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>> GROUP BY SUBSTRING(itemcallnumber,1,1)
>>> UNION ALL
>>> SELECT "All Classes" AS "CLASS", SUM(statistics.type) AS 'NUMBER'
>>> FROM borrowers
>>>   LEFT JOIN statistics ON
>>> (statistics.borrowernumber=borrowers.borrowernumber)
>>>   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
>>>   LEFT JOIN biblioitems ON (biblioitems.biblioitemnumber =
>>> items.biblioitemnumber)
>>> WHERE statistics.type IN ('issue', 'renew')
>>>
>>> Basically with UNION ALL you can create other queries that don't have to
>>> be tied to the data of the previous queries so long as the columns line
>>> up.
>>>
>>>
>>>
>>> On Thu, Mar 12, 2020 at 3:04 AM muiru james 
>>> wrote:
>>>
 Hi Rogan and Team,

 I really appreciate your assistance towards my problem. The query does
 indeed work to answer my need. Another query was also pushed to me towards
 the same.

 I'm now looking for a way to SUM my answer to get the total number of
 books issued. Any suggestions would be most welcome for any of the 2
 queries. The other SQL query is as below: -

 SELECT CONCAT(SUBSTRING(itemcallnumber,1,1), '00') AS "CLASS",
 count(statistics.type) AS 'NUMBER'
 FROM borrowers
   LEFT JOIN statistics ON
 (statistics.borrowernumber=borrowers.borrowernumber)
   LEFT JOIN items ON (items.itemnumber = statistics.itemnumber)
   LEFT JOIN biblioitems ON

Re: [Koha] tiny bit of help needed with report

[rogan . hamby]

Hi Lisa,

If you're just looking to use the branch column in statistics you can add
it to your select list and then to the group by.  So the select line
would become something like this:

SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
branch, count(*) AS Checkouts
FROM statistics
WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09
16:59:59'
AND statistics.type = 'issue'
GROUP BY 1, 2, 3, 4;



On Fri, May 15, 2020 at 9:48 AM Lisa Peel  wrote:

> Hello
>
> I apologise for the simplicity of this question but could really use some
> help.
>
> I want to use the Hourly report in the KOHA wiki for ‘Checkouts by Hour in
> Selected Date Range’ as below
>
> SELECT YEAR(statistics.datetime) AS Year, MONTH(statistics.datetime) AS
> Month, DAY(statistics.datetime) AS Day, HOUR(statistics.datetime) AS Hour,
> count(*) AS Checkouts
> FROM statistics
> WHERE statistics.datetime BETWEEN '2014-01-06 13:00:59' AND '2014-01-09
> 16:59:59'
> AND statistics.type = 'issue'
> GROUP BY Year, Month, Day, Hour
>
> However, I also need to add branch code as well so I can determine the
> number of checkouts by library but I am struggling to know what extra code
> to add? My knowledge of SQL is very, very limited and any help would be
> really appreciated
>
> Regards
>
> Lisa
>
> Mrs Lisa Peel
> Head of Learning Resources, SEG Services
> Swiss Education Group
> 
> Avenue des Alpes 62
> CH-1820 Montreux, Switzerland
> Mobile: +41 (0)79 228 0151
> Skype: biblio1897
> www.swisseducation.com
> Facebook | Instagram<
> https://www.instagram.com/swisseducationgroup/> | Linkedin<
> https://www.linkedin.com/company/seg-swiss-education-group>
>
> [http://signature.swisseducation.com/SEG2019.jpg]<
> https://www.swisseducation.com/en/>
>
> Confidentiality and Disclaimer:
> This message contains confidential information and is intended only for
> the individual named.
> Email messages are provided for informational purposes, therefore the
> sender does not accept
> liability for any errors or omissions in the contents of this message.
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] delete content of a biblio instance

[rogan . hamby]

So, just to make sure I understand, deleting all the bibs and associated
items but leaving everything else, right?

On Tue, Jul 7, 2020 at 9:40 AM Heinz-Jürgen Oertel 
wrote:

> Hello
>
> I'm looking for a way to delete the complete content of a biblio instance,
> not
> the instance itself in order to keep "Item types", "Patron categories" etc.
> Any hint is very welcome. After deleting the content,, I want to import
> new
> data via marcxml import.
>
> Greetings
> Heinz
>
>
>
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] delete content of a biblio instance

[rogan . hamby]

Well, it depends.

If you only have bibs and items loaded this should work:

SET FOREIGN_KEY_CHECKS = 0;
truncate biblio;
truncate biblioitems;

truncate items;
truncate auth_header;
truncate sessions;
truncate zebraqueue;
truncate biblio_metadata;
SET FOREIGN_KEY_CHECKS = 1;

However, if you've had this as a living system with transactions you will
also need to look at cleaning out a lot of other tables such as statistics,
issues, etc



On Tue, Jul 7, 2020 at 11:17 AM Luis Moises Rojas 
wrote:

> Hi,
> That was my question a few days ago, and there is no way.
>
> I don't know if there is any answer now
>
> El mar., 7 de jul. de 2020 9:40 AM, Heinz-Jürgen Oertel <
> hj.oer...@t-online.de> escribió:
>
> > Hello
> >
> > I'm looking for a way to delete the complete content of a biblio
> instance,
> > not
> > the instance itself in order to keep "Item types", "Patron categories"
> etc.
> > Any hint is very welcome. After deleting the content,, I want to import
> > new
> > data via marcxml import.
> >
> > Greetings
> > Heinz
> >
> >
> >
> > ___
> >
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] delete content of a biblio instance

[rogan . hamby]

Those are commands to run from a MySQL command prompt.

SET FOREIGN_KEY_CHECKS = 0;  basically turns off FK integrity so it should
always be used carefully and on a live system only as a last resort. The
truncates cleanout tables and the last one turns the FK checks back on.

Every rare now and then in a migration, I may use this if I've loaded bibs
and items and want to reload them before other loading is done.

On Tue, Jul 7, 2020 at 1:32 PM Heinz-Jürgen Oertel 
wrote:

> Am Dienstag, 7. Juli 2020, 17:55:20 CEST schrieb rogan.ha...@gmail.com:
> > Well, it depends.
>
> to your first question. It is not a used running system yet. I just used
> it to
> get familiar with it. The first import in a new biblio was not successful,
> not
> the correct import data used. Now I like to empty it and import a new
> export
> from another system.
>
> > If you only have bibs and items loaded this should work:
> >
> > SET FOREIGN_KEY_CHECKS = 0;
> > truncate biblio;
> > truncate biblioitems;
> >
> > truncate items;
> > truncate auth_header;
> > truncate sessions;
> > truncate zebraqueue;
> > truncate biblio_metadata;
> > SET FOREIGN_KEY_CHECKS = 1;
>
> I assume that are all data base commands to the biblio data base?
>
> > However, if you've had this as a living system with transactions you will
> > also need to look at cleaning out a lot of other tables such as
> statistics,
> > issues, etc
>
> see above.
>
> Thanks and Greetings
> Heinz
>
> >
> >
> > On Tue, Jul 7, 2020 at 11:17 AM Luis Moises Rojas <
> lmoisesro...@gmail.com>
> >
> > wrote:
> > > Hi,
> > > That was my question a few days ago, and there is no way.
> > >
> > > I don't know if there is any answer now
> > >
> > > El mar., 7 de jul. de 2020 9:40 AM, Heinz-Jürgen Oertel <
> > >
> > > hj.oer...@t-online.de> escribió:
> > > > Hello
> > > >
> > > > I'm looking for a way to delete the complete content of a biblio
> > >
> > > instance,
> > >
> > > > not
> > > > the instance itself in order to keep "Item types", "Patron
> categories"
> > >
> > > etc.
> > >
> > > > Any hint is very welcome. After deleting the content,, I want to
> import
> > > > new
> > > > data via marcxml import.
> > > >
> > > > Greetings
> > > >
> > > > Heinz
> > > >
> > > > ___
> > > >
> > > > Koha mailing list  http://koha-community.org
> > > > Koha@lists.katipo.co.nz
> > > > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
> > >
> > > ___
> > >
> > > Koha mailing list  http://koha-community.org
> > > Koha@lists.katipo.co.nz
> > > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
> --
> mit freundlichen Grüßen aus Halle (Saale)
>Heinz-Jürgen Oertel
>
>
>
___

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

Re: [Koha] ids exist in both tables *borrowers* and *deletedborrowers*

[rogan . hamby]

Hi Heinrich,

You may be running into this issue:
https://wiki.koha-community.org/wiki/DBMS_auto_increment_fix



On Tue, Jul 14, 2020 at 3:18 AM Heinrich Hartl  wrote:

>  From koha/about 
>
>
> Problems found
>
> Patrons
> The following ids exist in both tables *borrowers* and *deletedborrowers*:
> 57
>
>
> Yes, there is a borrowernr 57 in the table deletedborrowers with surname
> "test".
>
> And there is also a borrowernr. 57 in the table borrowers (this one is a
> real user of the library)
>
> What should I do to get rid of the deleted borrower 57?
>
> Regards
>
> Heinrich
>
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] koha script wiki

[rogan . hamby]

Same, I can't imagine anyone objecting to more useful stuff on the wiki.

On Thu, Nov 5, 2020 at 4:44 PM Michael Kuhn  wrote:

> Hi Alvaro
>
>  > Would it be possible to create a koha wiki for sharing scripts? Koha
>  > has reports, css and js wikis for sharing them with the community. I
>  > guess a way to share scripts might be an enhancement to koha
>  > ressources.
>  >
>  > Sometimes, writing scripts is a fast, cheap and reliable way to solve
>  > very specific issues. So sharing these resources might help the koha
>  > community.
>
> As far as I know everyone is free to add useful stuff to the Koha wiki.
>
> The pages you mention are probably
>
> * https://wiki.koha-community.org/wiki/SQL_Reports_Library
> * https://wiki.koha-community.org/wiki/HTML_%26_CSS_Library
> * https://wiki.koha-community.org/wiki/JQuery_Library
>
> They all are just articles in the Koha wiki so I would assume you could
> just create another page, like "Bash script Library" or something... If
> you need help with adding a wiki page just tell me.
>
> Hope this helps.
>
> Best wishes: Michael
> --
> Geschäftsführer · Diplombibliothekar BBS, Informatiker eidg. Fachausweis
> Admin Kuhn GmbH · Pappelstrasse 20 · 4123 Allschwil · Schweiz
> T 0041 (0)61 261 55 61 · E m...@adminkuhn.ch · W www.adminkuhn.ch
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] sql query

[rogan . hamby]

Hello,

You're 99% of the way there already, you are already using the items table
so you just need to add "items.ccode,items.location," in the appropriate
place, like this:


SELECT items.Barcode,biblio.Author,biblio.Title AS
Titles,biblioitems.Pages,biblioitems.Editionstatement AS
Edition,biblio.Copyrightdate AS Year,items.Price,biblioitems.publishercode
AS Publisher,biblioitems.Place,biblio.unititle AS Subject,items.location AS
Department,biblioitems.itemtype AS Department,items.itype AS
Status,biblioitems.url AS
OnlineURL,
items.ccode,items.location,
items.Dateaccessioned,biblioitems.ISBN,biblio.notes,biblio.Serial,items.Issues,items.Renewals
FROM items
LEFT JOIN biblioitems ON
(items.biblioitemnumber=biblioitems.biblioitemnumber)
LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
ORDER BY LPAD(items.barcode,40,' ') ASC

On Tue, Nov 10, 2020 at 3:50 AM Yatheesh lis  wrote:

> Hi
>
> I would like to add the following fields to sql query after OnlineURL
>
> koha 19.05
>
> 1. 8 - Collection code
> 2.c - Shelving location
>
>
>
> SELECT items.Barcode,biblio.Author,biblio.Title AS
> Titles,biblioitems.Pages,biblioitems.Editionstatement AS
> Edition,biblio.Copyrightdate AS Year,items.Price,biblioitems.publishercode
> AS Publisher,biblioitems.Place,biblio.unititle AS Subject,items.location AS
> Department,biblioitems.itemtype AS Department,items.itype AS
> Status,biblioitems.url AS
>
> OnlineURL,items.Dateaccessioned,biblioitems.ISBN,biblio.notes,biblio.Serial,items.Issues,items.Renewals
> FROM items
> LEFT JOIN biblioitems ON
> (items.biblioitemnumber=biblioitems.biblioitemnumber)
> LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber)
> ORDER BY LPAD(items.barcode,40,' ') ASC
>
> please help me
> --
>
> Y.Yatheesh Babu
> 8247838231
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] Koha wiki update/replacement

[rogan . hamby]

I don't have strong opinions about whether or not to change wiki softwares
but I do have quite a bit of experience in using DokuWiki and while it does
have its own syntax it's not burdensome or awkward.  Most users use the
formatting toolbar options and don't worry about the syntax much.



On Wed, Mar 10, 2021 at 11:49 AM Caroline Cyr-La-Rose <
caroline.cyr-la-r...@inlibro.com> wrote:

> Hello Koha community!
>
> There has been discussion about updating or replacing the wiki
> (https://wiki.koha-community.org). The software behind the current wiki
> is out of date and we need to upgrade it. Or we could replace it with
> something more lightweight or more user-friendly. The current wiki is a
> gold mine of information, but that good information is hard to find.
>
> This email is an attempt to start a conversation about the best plan of
> action to upgrade our wiki.
>
> The main argument for changing is that the MediaWiki version we are
> using is obsolete.
>
> - Should we continue with a more recent version of MediaWiki or change
> the software?
>
> It has been argued that maybe MediaWiki is a bit to complex for our
> needs. DokuWiki (https://www.dokuwiki.org/dokuwiki) was cited as an
> option, but it's apparently too light and one needs to learn the special
> syntax.
>
> - If we change the software, do you have a suggestion?
>
> Some have suggested that we simply archive the current wiki and start
> fresh with a new one.
>
> - Would you be in favor of starting over?
>
>
> I'm sure I'm forgetting things, but my hope is only to get the ball
> rolling and that this project does not fall in the cracks once again. If
> you have any suggestion, comment, or concern about this project, please
> voice them by replying to this email.
>
> If you'd rather discuss "live" about this issue, the wiki question is
> often on the Development IRC meetings agenda. Next meeting is on March
> 24, 2021 at 20 UTC
> https://wiki.koha-community.org/wiki/Development_IRC_meeting_24_March_2021
>
> Thank you for your input!
>
> Caroline
>
> --
> Caroline Cyr La Rose, M.L.I.S.
> Librarian | Product Manager
>
> Phone: 1-833-465-4276, ext. 221
> caroline.cyr-la-r...@inlibro.com 
>
> INLiBRO | Document Technologies Specialists | www.inLibro.com
> 
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] data migrating from Evergreen to Koha

[rogan . hamby]

Hi George,

I'm not familiar with any documentation but both schemas are public and as
someone who does migrations to both routinely I don't think migrating
between them would be problematic at all though it will require a
familiarity with both database structures.

On Tue, Jun 11, 2024 at 6:45 AM George Mikuchadze 
wrote:

> Dear Colleagues,
> My problem is data migrating from Evergreen to Koha (any version from v3.22
> - v23.11).
> Are there any well-documented procedures for this migration and if yes how
> to get it?
>
> Thanks in advance
> George Mikuchadze
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

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

Re: [Koha] data migrating from Evergreen to Koha

[rogan . hamby]

Evergreen has tools both in the staff client and via command line to export
bibs with holdings embedded.

On Tue, Jun 11, 2024 at 8:36 AM Dr. Yesan S (DLIS) 
wrote:

> Hi
> If the data is in excel sheet it can be easily migrated to koha. I think,
> in Evergreen there must be a way forward to export data in either Marc
> format or in csv format.
>
> Do you need help contact me off the list.
>
> Regards
> Dr Yesan Sellan
> Assistant Professor
> Dept of Library and Information Science
> Bishop Heber College
> Tiruchirappalli 620017
> Tamil Nadu.
>
>
>
> On Tue, 11 Jun 2024, 5:40 pm ,  wrote:
>
>> Hi George,
>>
>> I'm not familiar with any documentation but both schemas are public and as
>> someone who does migrations to both routinely I don't think migrating
>> between them would be problematic at all though it will require a
>> familiarity with both database structures.
>>
>> On Tue, Jun 11, 2024 at 6:45 AM George Mikuchadze 
>> wrote:
>>
>> > Dear Colleagues,
>> > My problem is data migrating from Evergreen to Koha (any version from
>> v3.22
>> > - v23.11).
>> > Are there any well-documented procedures for this migration and if yes
>> how
>> > to get it?
>> >
>> > Thanks in advance
>> > George Mikuchadze
>> > ___
>> >
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>> >
>> ___
>>
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>>
>
___

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