Custom SQL Query for LIbrary Overdues

Hi,

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 binfo.id as BookID, binfo.name as BookName, binfo.producer as BookAuthoer
, binfo.returnExpected as DueDate
, pinfo.firstname as FirstName, pinfo.surname as LastName, pinfo.email as Email
, finfo.homeAddress as HomeAddress, finfo.homeAddressDistrict as CityStateZip
, ainfo.firstname as ParentFirst, ainfo.surname as ParentLast, ainfo.email 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 gibbonedu.com library, with the title Overdue Library Loans. Sync your queries and it should appear. Thanks again! Ross.