[koha] SQL Report for withdrawn books.
I have the below query which runs well without the date filter. On including the date placeholder, it gives me zero results. What am I missing? I need to be able to select a date range. Please help SELECT biblio.author AS Author, biblio.title AS Title, biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS Call_Number,items.barcode AS Barcode_Number, items.price AS Price, items.itemlost_on AS 'Date Lost' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype= <<Section|itemtypes>> AND items.itemlost=<<Status|lost>> AND items.itemlost_on BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> ORDER BY items.itemcallnumber ASC
Hi James You need to define a date format /tell mysql that you are comparing dates with DATE_FORMAT(YOURDATE, '%yyyy-%mm-%dd') so your query shall be something like: I´m not sure how will it work with the dates picklers SELECT biblio.author AS Author, biblio.title AS Title, biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS Call_Number,items.barcode AS Barcode_Number, items.price AS Price, items.itemlost_on AS 'Date Lost' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype= <<Section|itemtypes>> AND items.itemlost=<<Status|lost>> AND items.itemlost_on BETWEEN DATE_FORMAT( <<Between (yyyy-mm-dd)|date>> , '%yyyy-%mm-%dd') AND DATE_FORMAT(<<and (yyyy-mm-dd)|date>> , '%yyy-%mm-%dd') ORDER BY items.itemcallnumber ASC |----------------------------------------------------------------------------------------| Stay safe / Cuídate/ Reste sécurisé *7* Switch off as you go / Apaga lo que no usas / Débranchez au fur et à mesure. *q *Recycle always / Recicla siempre / Recyclez toujours P Print only if absolutely necessary / Imprime solo si es necesario / Imprimez seulement si nécessaire Le ven. 27 nov. 2020 à 04:25, muiru james <muirunyeri@gmail.com> a écrit :
I have the below query which runs well without the date filter. On including the date placeholder, it gives me zero results. What am I missing? I need to be able to select a date range. Please help
SELECT biblio.author AS Author, biblio.title AS Title, biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS Call_Number,items.barcode AS Barcode_Number, items.price AS Price, items.itemlost_on AS 'Date Lost' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype= <<Section|itemtypes>> AND items.itemlost=<<Status|lost>> AND items.itemlost_on BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> ORDER BY items.itemcallnumber ASC _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi, when using the |date, use the datepicker to set the date. Koha will automatically convert it to the correct format for the database in your query. I think the issue is that items.itemlost_on is a datetime. So use DATE() to get better results and use the datepicker to enter the dates! AND DATE(items.itemlost_on) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and... Hope this helps, Katrin On 27.11.20 10:25, muiru james wrote:
I have the below query which runs well without the date filter. On including the date placeholder, it gives me zero results. What am I missing? I need to be able to select a date range. Please help
SELECT biblio.author AS Author, biblio.title AS Title, biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS Call_Number,items.barcode AS Barcode_Number, items.price AS Price, items.itemlost_on AS 'Date Lost' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype= <<Section|itemtypes>> AND items.itemlost=<<Status|lost>> AND items.itemlost_on BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> ORDER BY items.itemcallnumber ASC _______________________________________________
Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Hi, Thanks Katrina and Alvaro for your time and effort. Katrin's suggestion solved my problem. If you have an idea on my string on batch editing dates for withdrawn/lost items to set an earlier one I would be more than happy Thank you all Regards James On Sat, Nov 28, 2020 at 12:39 PM Katrin Fischer <katrin.fischer.83@web.de> wrote:
Hi,
when using the |date, use the datepicker to set the date. Koha will automatically convert it to the correct format for the database in your query. I think the issue is that items.itemlost_on is a datetime. So use DATE() to get better results and use the datepicker to enter the dates!
AND DATE(items.itemlost_on) BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and...
Hope this helps,
Katrin
On 27.11.20 10:25, muiru james wrote:
I have the below query which runs well without the date filter. On including the date placeholder, it gives me zero results. What am I missing? I need to be able to select a date range. Please help
SELECT biblio.author AS Author, biblio.title AS Title, biblioitems.publishercode AS Publisher,biblioitems.publicationyear AS Date_of_Publication, biblioitems.isbn AS ISBN, items.itemcallnumber AS Call_Number,items.barcode AS Barcode_Number, items.price AS Price, items.itemlost_on AS 'Date Lost' FROM items LEFT JOIN biblioitems ON (items.biblioitemnumber=biblioitems.biblioitemnumber) LEFT JOIN biblio ON (biblioitems.biblionumber=biblio.biblionumber) WHERE items.itype= <<Section|itemtypes>> AND items.itemlost=<<Status|lost>> AND items.itemlost_on BETWEEN <<Between (yyyy-mm-dd)|date>> AND <<and (yyyy-mm-dd)|date>> ORDER BY items.itemcallnumber ASC _______________________________________________
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
participants (3)
-
Alvaro Cornejo -
Katrin Fischer -
muiru james