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

[Suresh Kumar Tejomurtula]

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.
-- 
Regards
T. Suresh Kumar