- Article Type: General
- Product: Aleph
- Product Version: 20
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.)
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 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:.
select substr(z31_rec_key,1,12) 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:
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.
- Article last edited: 10/8/2013