Those Pesky ILL Requests that No One Picks up

Greetings fellow data nerds (and I mean that in every good way possible),

Earlier today, a question came up via Facebook from a customer who was looking for a query to find out the number of, or percentage of, ILLiad items requested but not picked up by the user. 

As this sort of thing had come up fairly recently in the context of a support request from another site, I already had some reasonably similar queries that would, with a little tweaking, suffice for this.

Please note: I make no claim that this is the ONLY way to go about this, but it does seem to work pretty well for the intended purpose.

As there are different processes (and thus things to key on) for electronic articles and physical loans, I elected to separate the two processes into distinct queries and take slightly different approaches with the query design.

The first, "Articles Delivered to Web and not Viewed" query will allow you to set date/time variables, and a site variable if you have delivery locations you wish to separate out, for whatever time interval and site is your focus.

Note: This doesn't look at whether the Article in question is still at Delivered to Web, so it'll be much more effective/useful if you focus on articles already past your institution’s article retention date (the default is 30 days, but your institution may be doing something different).

This Electronic Article-specific query is as follows:

-----------------------------------------------------------------

--Articles Requested But Not Viewed

DECLARE @BeginDate DateTime, @EndDate DateTime, @SITECode nvarchar(10) ;

-- EDIT THESE VALUES BEFORE RUNNING SCRIPT --

SET @BeginDate = '2017-01-01 00:00:00.000';

SET @EndDate = '2017-02-01 00:00:00.000';

SET @SITECode = 'ILL';

-- DO NOT EDIT BELOW THIS LINE --

 

SELECT Distinct u.NVTGC as Site, t.TransactionNumber, t.Username, u.FirstName, u.Lastname, t.IFMCost, t.IFMPaid, t.CreationDate, t.NotwantedAfter, k.DateTime as Delivered_To_Web, k.ChangedTo

FROM Transactions t

Join Users u on t. Username = u.Username

Join Tracking k on t.TransactionNumber = k.Transactionnumber

Join History h on t.TransactionNumber = h.TransactionNumber

Where t.ProcessType = 'Borrowing'

and t.RequestType = 'Article'

and NVTGC = @SITECode

and k.ChangedTo in ('Delivered to Web')

and k.Datetime BETWEEN @BeginDate AND @EndDate

and t.TransactionNumber not in

(

Select Distinct TransactionNumber from History

where Entry = 'PDF Viewed'

)

Order by t.TransactionNumber, t.CreationDate, t.NotwantedAfter, k.DateTime

-----------------------------------------------------------------

The second query was intended to look for Loans requested and never picked up, and due to the way the tables involved are set up and the possibility of duplication under certain circumstances, we had to go a slightly different (and a little more complicated) direction with it in terms of query design, but temporary tables are really cool things.

This “Loans Requested and not Picked Up” query works essentially the same way as the first one. Simply specify the variables at the top for time frame and site (if applicable) and the query will pull the Transactions that were never picked up.

-----------------------------------------------------------------

--Loans Requested But Not Picked Up

DECLARE @BeginDate DateTime, @EndDate DateTime, @SITECode nvarchar(10) ;

-- EDIT THESE VALUES BEFORE RUNNING SCRIPT --

SET @BeginDate = '2017-01-01 00:00:00.000';

SET @EndDate = '2017-02-01 00:00:00.000';

SET @SITECode = 'ILL';

-- DO NOT EDIT BELOW THIS LINE --

 

IF OBJECT_ID('tempdb..#ReportDataSet') IS NOT NULL

DROP TABLE #ReportDataSet;

--CREATE TEMP TABLE #ReportDataSet and populate with TN, StatusChange, Position

SELECT t.TransactionNumber, k.ChangedTo as StatusChange, ROW_NUMBER() OVER(PARTITION BY t.TransactionNumber ORDER BY k.DateTime) as Position

INTO #ReportDataSet

FROM Transactions t

INNER JOIN Tracking k ON t.TransactionNumber = k.TransactionNumber

WHERE t.ProcessType = 'Borrowing'

AND t.RequestType = 'Loan'

AND k.ChangedTo in ('Item Not Picked Up', 'Awaiting Return Label Printing')

AND k.Datetime BETWEEN @BeginDate AND @EndDate

AND t.TransactionNumber not in

(

Select Distinct TransactionNumber from Tracking

where ChangedTo = 'Checked Out to Customer'

);

Select u.NVTGC as Site, r.TransactionNumber, t.Username, u.FirstName, u.Lastname, t.IFMCost, t.IFMPaid, t.DueDate, k.DateTime as Returned, k.ChangedTo

FROM #ReportDataSet r

Join Transactions t on r.TransactionNumber = t.TransactionNumber

Join Users u on t. Username = u.Username

Join Tracking k on t.TransactionNumber = k.Transactionnumber and r.StatusChange = k.ChangedTo

Where r.Position = 1

and t.ProcessType = 'Borrowing'

and t.RequestType = 'Loan'

and u.NVTGC = @SITECode

and k.Datetime BETWEEN @BeginDate AND @EndDate

Order by t.TransactionNumber, t.DueDate, k.DateTime 

-----------------------------------------------------------------

As an FYI for anyone on a multi-site system (Shared Server), the only change you would need to these queries would be to one table name.  Just change the table "Users" to "UsersALL" and everything else should work the same for you, although that site specification variable would likely be more important to you than to the Single-site folks.

Note: As a general rule, if you’re not already using a read-only account to run these queries, I would highly recommend looking into that.  Like yesterday.  (No joke.) 

Happy querying.

- Kevin