‘Unsuccessful’ would be a search where no search results were returned. ‘Successful’ would be searches that retrieved results Thanks, Kerrie Stevens From: Coehoorn, Joel <jcoehoorn@york.edu> Sent: Wednesday, 11 December 2019 3:54 PM To: Kerrie Stevens <kerrie.stevens@ac.edu.au> Subject: Re: [Koha] SQL help Define "unsuccessful" On Tue, Dec 10, 2019, 10:13 PM Kerrie Stevens <kerrie.stevens@ac.edu.au<mailto:kerrie.stevens@ac.edu.au>> wrote: Our overall library collection is currently split between 2 different catalogues. The other catalogue can produce a report showing the number of opac searches (total, successful and unsuccessful) for each day of each month for the year. Can anyone help with how I can replicate that in Koha? Thank you, Kerrie Stevens Alphacrucis College Melbourne, Australia _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
Kerrie, Which version of Koha are you running? You would likely need to modify the following report found on the Koha Reports Library <https://wiki.koha-community.org/wiki/SQL_Reports_Library> to meet your need. https://wiki.koha-community.org/wiki/SQL_Reports_Library#Zero_Results_Search... M. _________________________________________ *Michael J. Sutherland* University Libraries Virginia Tech sudrland@vt.edu | 540.231.9669 <+15402319669> On Tue, Dec 10, 2019 at 11:57 PM Kerrie Stevens <kerrie.stevens@ac.edu.au> wrote:
‘Unsuccessful’ would be a search where no search results were returned. ‘Successful’ would be searches that retrieved results Thanks,
Kerrie Stevens
From: Coehoorn, Joel <jcoehoorn@york.edu> Sent: Wednesday, 11 December 2019 3:54 PM To: Kerrie Stevens <kerrie.stevens@ac.edu.au> Subject: Re: [Koha] SQL help
Define "unsuccessful"
On Tue, Dec 10, 2019, 10:13 PM Kerrie Stevens <kerrie.stevens@ac.edu.au <mailto:kerrie.stevens@ac.edu.au>> wrote: Our overall library collection is currently split between 2 different catalogues. The other catalogue can produce a report showing the number of opac searches (total, successful and unsuccessful) for each day of each month for the year. Can anyone help with how I can replicate that in Koha?
Thank you,
Kerrie Stevens Alphacrucis College Melbourne, Australia _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha
We have version 18.11.11 and I’ve looked through the reports library but can’t see anything that looks similar to be tweaked into what I need. Thank you, Kerrie Stevens From: Michael Sutherland <sudrland@vt.edu> Sent: Thursday, 12 December 2019 12:59 AM To: Kerrie Stevens <kerrie.stevens@ac.edu.au> Cc: joel.coehoorn@york.edu; koha@lists.katipo.co.nz Subject: Re: [Koha] SQL help Kerrie, Which version of Koha are you running? You would likely need to modify the following report found on the Koha Reports Library <https://wiki.koha-community.org/wiki/SQL_Reports_Library> to meet your need. https://wiki.koha-community.org/wiki/SQL_Reports_Library#Zero_Results_Search... M. _________________________________________ Michael J. Sutherland University Libraries Virginia Tech sudrland@vt.edu<mailto:sudrland@vt.edu> | 540.231.9669<tel:+15402319669> On Tue, Dec 10, 2019 at 11:57 PM Kerrie Stevens <kerrie.stevens@ac.edu.au<mailto:kerrie.stevens@ac.edu.au>> wrote: ‘Unsuccessful’ would be a search where no search results were returned. ‘Successful’ would be searches that retrieved results Thanks, Kerrie Stevens From: Coehoorn, Joel <jcoehoorn@york.edu<mailto:jcoehoorn@york.edu>> Sent: Wednesday, 11 December 2019 3:54 PM To: Kerrie Stevens <kerrie.stevens@ac.edu.au<mailto:kerrie.stevens@ac.edu.au>> Subject: Re: [Koha] SQL help Define "unsuccessful" On Tue, Dec 10, 2019, 10:13 PM Kerrie Stevens <kerrie.stevens@ac.edu.au<mailto:kerrie.stevens@ac.edu.au><mailto:kerrie.stevens@ac.edu.au<mailto:kerrie.stevens@ac.edu.au>>> wrote: Our overall library collection is currently split between 2 different catalogues. The other catalogue can produce a report showing the number of opac searches (total, successful and unsuccessful) for each day of each month for the year. Can anyone help with how I can replicate that in Koha? Thank you, Kerrie Stevens Alphacrucis College Melbourne, Australia _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz><mailto:Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz>> https://lists.katipo.co.nz/mailman/listinfo/koha _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz> https://lists.katipo.co.nz/mailman/listinfo/koha
On Wed, Dec 11, 2019 at 5:53 PM Kerrie Stevens <kerrie.stevens@ac.edu.au> wrote:
We have version 18.11.11 and I’ve looked through the reports library but can’t see anything that looks similar to be tweaked into what I need.
Try this, for year 2019 SELECT month, day, suc+uns AS total, suc AS succesful, uns AS unsuccesful FROM ( SELECT CASE WHEN A.m1 IS NULL THEN A.m2 ELSE A.m1 END AS month, CASE WHEN A.d1 IS NULL THEN A.d2 ELSE A.d1 END AS day, CASE WHEN A.suc IS NULL THEN 0 ELSE A.suc END AS suc, CASE WHEN A.uns IS NULL THEN 0 ELSE A.uns END AS uns FROM ( SELECT * FROM ( SELECT month(time) AS m1, day(time) AS d1, count(*) AS suc FROM search_history WHERE total > 0 AND year(time) = '2019' GROUP BY m1,d1 ) AS S LEFT JOIN ( SELECT month(time) AS m2, day(time) AS d2, count(*) AS uns FROM search_history WHERE total = 0 AND year(time) = '2019' GROUP BY m2,d2 ) AS U ON S.m1 = U.m2 and S.d1 = U.d2 UNION SELECT * FROM ( SELECT month(time) AS m1, day(time) AS d1, count(*) AS suc FROM search_history WHERE total > 0 AND year(time) = '2019' GROUP BY m1,d1 ) AS SR RIGHT JOIN ( SELECT month(time) AS m2, day(time) AS d2, count(*) AS uns FROM search_history WHERE total = 0 AND year(time) = '2019' GROUP BY m2,d2 ) AS UR ON SR.m1 = UR.m2 and SR.d1 = UR.d2 ) AS A ORDER BY month,day ) AS B; Hope that helps Bernardo
participants (3)
-
Bernardo Gonzalez Kriegel -
Kerrie Stevens -
Michael Sutherland