Counts of overdue letters for each letter number

• Article Type: General
• Product: Aleph
• Product Version: 20

Description:
I have been asked to prepare a report with counts of overdue letters per letter number (1st to 4th warning) grouped by patron status for a certain sublibrary.

My thoughts are:
- I can't answer this in ARC
- Overdue letters are not logged as events in Z35
- In find letter numbers in Z1003, but I can't identify a patron(-status). Join with Z36?
- I probably have to work with Z36 and count Z36-LETTER-NUMBER. But then I need to make sure somehow to count each letter only once and not to miss it, before it transforms to a Z36H.

Resolution:
[From Christine Moulen, MIT, on Global Aleph Users list:]

I think I would work with Z36.
Any given loan should only exist once, either in Z36 or in Z36H, but not both.
So I think you could count in both tables how many loans have Z36_LETTER_NUMBER >= 3 (we send up to 4 letters), with Z36_LETTER_DATE in the range of last year.
Then add the totals together.

Another idea, could you possibly use Z309? We log our overdue letters there. But that might not have sufficient details to get patron status or join to a table that has it.

[From Tonny van Driel, Utrecht:]

When you look at the Letter number (in Z36/Z36H or even in ARC where the letter number is part of the 'Loan Other Information' in the package Loan) you will see the highest overdue letter number that is sent for the loan.
In my case (in my libary), when I count these Letter numbers I will not get the number of overdue letters that are sent. That is because our library permits renewal of the loan even if the first, second and third overdue letter is made. When you renew a loan, the letter number goes back to 0 and so does the letter date. I would use Z309 to know how many overdue letters have been sent. This Z309 also shows the loan number (Z309_REC_KEY_2), so you can link to loan number in Z36/Z36H for the patron status of the loan event.
The first 12 positions of the Z309_rec_key are the ID of the patron, so perhaps you prefer this to find the patron status.
I have no nice solution for you, but I merely wanted to warn you for the renewal thing.

[Response from Christine M:]

That reminds me, you should also be aware of this tab100 setting, which might reset the letter number:

!# RESET-LETTER-NUMBER
!# Values:Y N Default: N
!# Type: Text; Max Length: 01
!# tab100 of library: Yes; tab100_<server_type>: No.
!
! This variable sets whether an online recall of an item will reset
! the loan's Z36-LETTER-NUMBER field.

[From Marcus Zerbst:]

Z309 is not an option for me since we don't fill it (all transactions set to N in tab_circ_log.lng). z36 would work, but following a tip I now read z31 instead, the fines table, and join it with z305. This is my result, a bit generalized:

create table sublib_fines as
select z31_rec_key, z31_description, z31_date_x from z31 where z31_sub_library = 'sublibcode'
and z31_date_x like 'YYYY%'
and z31_description like '%text_from_tab18_col9%'
-- distinct transaction number from col.1 might work for you ;

select substr(z31_description,1,10), z305_bor_status, count(*) from sublib_fines, z305 where substr(z31_rec_key,1,12) = substr(z305_rec_key,1,12) GROUP BY substr(z31_description,1,10), z305_bor_status ;