• 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.