Skip to main content
ExLibris
  • Subscribe by RSS
  • Ex Libris Knowledge Center

    SQL to find sum of what patrons owe

    • Article Type: General
    • Product: Aleph
    • Product Version: 20, 21, 22, 23

    Description:
    I'm using SQL to get a list of graduating seniors that owe the library money -- I pulled the z305_sum from the z305 table, but the problem I'm having is that the number in the table looks like the conversion number -- example userid 03394 -- what I get from Z305 is $13.50 (her conversion fines) but all of her current fines (for items returned) are not added in here. The total sum from the circ gui is $34.00 -- where is that total sum from the borrower record being held so I can pull that info?

    (We would like to send a message to everyone who owes more than $30.00.)

    Resolution:
    The total sum is not actually stored. When it's needed, it's calculated from the Z31 (cash) amounts. Here is some SQL which gives you the sum:
    SQL-ABC50> select substr(z31_rec_key,1,12), sum(z31_sum) from xxx50.z31 where Z31_STATUS = 'O' and Z31_CREDIT_DEBIT = 'D' group by substr(z31_rec_key,1,12) order by sum(z31_sum) desc;

    The above will give you everything, with the highest amounts first. You can just disregard the ones at the end which are below xxxx amount.
    Note: In the cases where the user has *credits*, this will not be right. You may want to do this to find people who have credits:.
    sql> select substr(z31_rec_key,1,12) from z31 where Z31_CREDIT_DEBIT = 'C';

    The following is a more sophisticated SQL (supplied by Patrick Fiset at Universite du Quebec) which takes the credits into account and omits patrons owing less than $30.00:
    SQL> SELECT substr(Z31_REC_KEY,1,12) USER_ID, SUM(CASE WHEN z31_credit_debit='C' THEN z31_sum ELSE '0' END) - SUM(CASE WHEN z31_credit_debit='D' THEN z31_sum ELSE '0' END) TOTAL_DEBT FROM utr50.z31 WHERE Z31_STATUS='O'
    GROUP BY substr(Z31_REC_KEY,1,12)
    HAVING (SUM(CASE WHEN z31_credit_debit='C' THEN z31_sum ELSE '0' END) - SUM(CASE WHEN z31_credit_debit='D' THEN z31_sum ELSE '0' END))<-3000 ORDER BY TOTAL_DEBT;


    See also, KB 3919 and the p_cash_06 job.

    Additional Information

    z305-sum


    • Article last edited: 10/8/2013