The Default Copyright Query

The default Copyright query built into the ILLiad Client displays an alphabetical list of journal titles for items that have been requested five or more times from the last five years, based upon the displayed journal titles. The Copyright query utilizes the CreationDate field in the Transactions table. The CreationDate field contains the date and time the transaction was originally created and entered into the system.

SELECT DISTINCT PhotoJournalTitle FROM Transactions
WHERE TransactionStatus != 'Cancelled by ILL Staff'
AND TransactionStatus != 'Cancelled by Customer'
AND TransactionStatus != 'Awaiting Copyright Clearance'
AND ISNULL(CopyrightAlreadyPaid, 'No') != 'Yes'
AND DATEPART(year, CreationDate) = DATEPART(year, GETDATE())
AND PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5))
AND ProcessType = 'Borrowing'
GROUP BY PhotoJournalTitle
HAVING COUNT(PhotoJournalTitle) > 4
ORDER BY PhotoJournalTitle

Modifying the Default Copyright Query

The copyright.txt file is an optional file that exists to allow ILLiad sites increased flexibility in terms of how their ILLiad client deals with copyright. For instructions creating the copyright.txt file, please see Adding and Enabling the Copyright.txt File. If the above query is sufficient to your institution's needs and to your institution's interpretations of the rules governing copyright practices, there is no need for the additional copyright.txt file. However, if you need to modify this query for some reason, the copyright.txt file gives you the ability to do so. For example, some institutions interpret copyright law as requiring that the last six years of requested journal titles be considered, rather than just five. If this is the case, the modified query would read as follows:

SELECT DISTINCT PhotoJournalTitle FROM Transactions
WHERE TransactionStatus != 'Cancelled by ILL Staff'
AND TransactionStatus != 'Cancelled by Customer'
AND TransactionStatus != 'Awaiting Copyright Clearance'
AND ISNULL(CopyrightAlreadyPaid, 'No') != 'Yes'
AND DATEPART(year, CreationDate) = DATEPART(year, GETDATE())
AND PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 6))
AND ProcessType = 'Borrowing'
GROUP BY PhotoJournalTitle
HAVING COUNT(PhotoJournalTitle) > 4
ORDER BY PhotoJournalTitle

Another common modification to the copyright query is a change to differentiate between sites in a Shared Server ILLiad system so that each site tracks their own number of requests for "fair use" etc. This is done by referencing the Users Table as in the following query:

SELECT DISTINCT PhotoJournalTitle FROM Transactions t inner join UsersALL u on 
t.username = u.username
WHERE TransactionStatus != 'Cancelled by ILL Staff'
AND TransactionStatus != 'Cancelled by Customer'
AND TransactionStatus != 'Awaiting Copyright Clearance'
AND ISNULL(CopyrightAlreadyPaid, 'No') != 'Yes'
AND DATEPART(year, CreationDate) = DATEPART(year, GETDATE())
AND PhotoJournalYear > CONVERT(varchar(4),(DATEPART(year, GETDATE()) - 5))
AND ProcessType = 'Borrowing'
GROUP BY PhotoJournalTitle
HAVING COUNT(PhotoJournalTitle) > 4
ORDER BY PhotoJournalTitle

Use these examples to create the best list for your copyright tracking needs. Remember that this list is showing journal titles of items that might need to be paid for copyright and that some article may get cancelled later in the process. The copyright report you run and submit is the actual list of items that need to be paid.