SQL help?

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

SQL help?

Kerrie Stevens-2
Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one...
I'd like to be able to copy & paste a number of borrower numbers into search box and get back a table showing those with outstanding loans:
(borrower number, name, and number of current loans)
Can cutting & pasting or searching on multiple borrower numbers be done in SQL reports?
This is way beyond my basic level of SQL understanding, so any advice/assistance would be greatly appreciated.

Thank you and have a great day wherever you may be!


Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt)
Director of Library Services, Alphacrucis College
Librarian - Melbourne Campus
HDR Liaison Librarian
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help?

Alvaro Cornejo
Hi

Mysql does not have the ability to read info from any other place than its
own tables.

You can, however write an script in any language you know to loop
through your borrowers file and check if they owe something.

This shall be done at server level, not koha.

Regards,

Alvaro
|----------------------------------------------------------------------------------------|
*7* Switch off as you go / Apaga lo que no usas /  Débranchez au fur et à
mesure.
 *q *Recycle always / Recicla siempre / Recyclez toujours
 P Print only if absolutely necessary / Imprime solo si es necesario /
Imprimez seulement si nécessaire


Le mar. 26 mai 2020 à 19:25, Kerrie Stevens <[hidden email]> a
écrit :

> Every year, we need to confirm graduating students have no outstanding
> loans so they can graduate. And every year I think I must ask for some help
> in doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into
> search box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans)
> Can cutting & pasting or searching on multiple borrower numbers be done in
> SQL reports?
> This is way beyond my basic level of SQL understanding, so any
> advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt)
> Director of Library Services, Alphacrucis College
> Librarian - Melbourne Campus
> HDR Liaison Librarian
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help?

Jonathan Druart
In reply to this post by Kerrie Stevens-2
Hello Kerrie,

I am not sure this is what you want, but if you create a SQL report
with this query you will see the patrons with outstanding loans:

SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*)
FROM issues
LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber
GROUP BY b.borrowernumber;

Regards,
Jonathan

Le mer. 27 mai 2020 à 02:24, Kerrie Stevens <[hidden email]> a écrit :

>
> Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into search box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans)
> Can cutting & pasting or searching on multiple borrower numbers be done in SQL reports?
> This is way beyond my basic level of SQL understanding, so any advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt)
> Director of Library Services, Alphacrucis College
> Librarian - Melbourne Campus
> HDR Liaison Librarian
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help?

Holger Meissner
Hi Kerrie,

if you don't mind hardcoding the borrowernumbers this should do the trick.

SELECT
    b.borrowernumber AS 'Borrowernumber',
    CONCAT(b.firstname, ' ', b.surname) AS 'Name',
    COUNT(i.issue_id) AS 'Outstanding loans'
FROM borrowers b
LEFT JOIN issues i ON b.borrowernumber = i.borrowernumber
WHERE b.borrowernumber IN (
    123,
    456,
    789
)
GROUP BY b.borrowernumber

Alternatively, you could use a BETWEEN in the WHERE clause, if the borrowernumbers are consecutive.

Regards,
Holger

-----Ursprüngliche Nachricht-----
Von: Koha <[hidden email]> Im Auftrag von Jonathan Druart
Gesendet: Mittwoch, 27. Mai 2020 11:00
An: Kerrie Stevens <[hidden email]>
Cc: [hidden email]
Betreff: Re: [Koha] SQL help?

Hello Kerrie,

I am not sure this is what you want, but if you create a SQL report with this query you will see the patrons with outstanding loans:

SELECT b.borrowernumber, concat(b.surname, ' ', b.firstname), count(*) FROM issues LEFT JOIN borrowers b on b.borrowernumber=issues.borrowernumber
GROUP BY b.borrowernumber;

Regards,
Jonathan

Le mer. 27 mai 2020 à 02:24, Kerrie Stevens <[hidden email]> a écrit :

>
> Every year, we need to confirm graduating students have no outstanding loans so they can graduate. And every year I think I must ask for some help in doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into search box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans) Can cutting &
> pasting or searching on multiple borrower numbers be done in SQL reports?
> This is way beyond my basic level of SQL understanding, so any advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt) Director
> of Library Services, Alphacrucis College Librarian - Melbourne Campus
> HDR Liaison Librarian _______________________________________________
>
> Koha mailing list  http://koha-community.org [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org [hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
Reply | Threaded
Open this post in threaded view
|

Re: SQL help?

Myka Kennedy Stephens
In reply to this post by Kerrie Stevens-2
Hi Kerrie,

The workflow that we've developed utilizes patron lists. When the list of
prospective graduates is prepared by the registrar's office, I put them all
into a patron list. Yes, this involves looking up individual students by
name, but I only have to do this once. After they are in the list, I use a
report to see who has an outstanding account balance and/or books still
checked out. I use this information to email the students to let them know
what they need to pay and/or return by our deadline for graduation. When it
is time to convert their accounts to alumni status, I use the batch patron
modification tool. This is super easy to use with patron lists and is
linked both from the patron list tool and the batch patron modification
tool. This year I also wrote a new report that gives me the email addresses
for all the patrons on the list, which made it easier to email them all
when their accounts were converted to congratulate them and let them know
about their alumni library privileges.

I will paste my SQL reports below in case you decide to go this route and
use patron lists.

Good luck,
Myka

Graduating Students Account Status
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,
    cardnumber,
    surname,
    firstname,
    email,
    COALESCE(i.CO, 0) AS checkouts,
    f.Owes
FROM
    borrowers b
JOIN
    patron_list_patrons USING (borrowernumber)
JOIN
    patron_lists ON (patron_list_patrons.patron_list_id =
patron_lists.patron_list_id)
LEFT JOIN
    (SELECT
        borrowernumber, COUNT(issue_id) AS CO
    FROM
        issues
    GROUP BY borrowernumber) i USING (borrowernumber)
LEFT JOIN
    (SELECT
        borrowernumber,
            FORMAT(SUM(accountlines.amountoutstanding), 2) AS Owes
    FROM
        accountlines
    GROUP BY accountlines.borrowernumber) f USING (borrowernumber)
WHERE
    patron_lists.patron_list_id = <<Enter patron list ID>> AND
(COALESCE(i.CO, 0) > 0 OR f.Owes > 0)

Email Addresses from a Patron List
SELECT
    CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=',
borrowernumber, '\">', borrowernumber, '</a>' ) AS borrowernumber,
    cardnumber,
    surname,
    firstname,
    email
FROM
    borrowers b
JOIN
    patron_list_patrons USING (borrowernumber)
JOIN
    patron_lists ON (patron_list_patrons.patron_list_id =
patron_lists.patron_list_id)
WHERE
patron_lists.patron_list_id = <<Enter patron list ID>>

On Tue, May 26, 2020 at 8:24 PM Kerrie Stevens <[hidden email]>
wrote:

> Every year, we need to confirm graduating students have no outstanding
> loans so they can graduate. And every year I think I must ask for some help
> in doing it more efficiently than searching borrower numbers one by one...
> I'd like to be able to copy & paste a number of borrower numbers into
> search box and get back a table showing those with outstanding loans:
> (borrower number, name, and number of current loans)
> Can cutting & pasting or searching on multiple borrower numbers be done in
> SQL reports?
> This is way beyond my basic level of SQL understanding, so any
> advice/assistance would be greatly appreciated.
>
> Thank you and have a great day wherever you may be!
>
>
> Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info&LibMgt)
> Director of Library Services, Alphacrucis College
> Librarian - Melbourne Campus
> HDR Liaison Librarian
> _______________________________________________
>
> Koha mailing list  http://koha-community.org
> [hidden email]
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>


--
Deaconess Myka Kennedy Stephens, MDiv, MSLIS
Seminary Librarian
Associate Professor of Theological Bibliography

555 West James Street
Lancaster, PA 17603
717-290-8704
[hidden email]
https://library.lancasterseminary.edu
_______________________________________________

Koha mailing list  http://koha-community.org
[hidden email]
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha