Custom SQL Query for LIbrary Overdues


We’ve been evaluating Gibbon and one point that came up was the ability to do a mail merge for sending letters home for overdue books. Thankfully there is Query Builder. I built the SQL query below to get the information we need.

select as BookID, as BookName, binfo.producer as BookAuthoer
, binfo.returnExpected as DueDate
, pinfo.firstname as FirstName, pinfo.surname as LastName, as Email
, finfo.homeAddress as HomeAddress, finfo.homeAddressDistrict as CityStateZip
, ainfo.firstname as ParentFirst, ainfo.surname as ParentLast, as ParentEmail
from gibbonLibraryItem as binfo
inner join gibbonPerson as pinfo on binfo.gibbonPersonIDStatusResponsible = pinfo.gibbonPersonID
inner join gibbonFamilyChild as fc on pinfo.gibbonPersonID = fc.gibbonPersonID
inner join gibbonFamilyAdult as fa on fc.gibbonFamilyID = fa.gibbonFamilyID and fa.contactPriority = 1
inner join gibbonFamily as finfo on fa.gibbonFamilyID = finfo.gibbonFamilyID
inner join gibbonPerson as ainfo on fa.gibbonPersonID = ainfo.gibbonPersonID
where binfo.status = ‘On Loan’ and binfo.returnExpected < now()`

I hope this is helpful to others and Query Builder lets you customize it quite easily.

Hello, thanks for contributing this, it is very kind of you. Are you OK for me to add this into the Query Builder centralised library? Ross.

No problem what-so-ever. :slight_smile:

Ralph, that is very generous of you. It is now in the Query Builder library, with the title Overdue Library Loans. Sync your queries and it should appear. Thanks again! Ross.