[Koha] SQL query on number of days to get a book

[Katrin Fischer]

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.