Thank you for sharing your reports! Maybe you'd like to add them to the reports library on the wiki too? https://wiki.koha-community.org/wiki/SQL_Reports_Library Katrin On 28.05.2018 16:25, Suresh Kumar Tejomurtula wrote:
Hi,
Query on orders(books) which are received in library and an email is triggered to members. This report may be useful in calculating number of days we are taking to get a suggested book. Here iam considering mail sent to a member as a value for calculating the number of days taken to get a book.
SELECT aqorders.ordernumber, aqorders.biblionumber,aqorders.datereceived,aqorders.basketno, biblio.title,biblio.author, items.barcode,suggestions.date, suggestions.suggesteddate, message_queue.time_queued,(TO_DAYS(message_queue.time_queued)-TO_DAYS(suggestions.suggesteddate)) as days FROM aqorders LEFT JOIN biblio on (aqorders.biblionumber=biblio.biblionumber) LEFT JOIN items on (biblio.biblionumber=items.biblionumber) LEFT JOIN suggestions on (suggestions.biblionumber=items.biblionumber) LEFT JOIN borrowers on (borrowers.borrowernumber=suggestions.suggestedby) LEFT JOIN message_queue on (message_queue.borrowernumber=suggestions.suggestedby) where suggestions.status='Available' and items.itype='BK' GROUP by suggestions.title
Hope this is useful for other members.