Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: typo it said "Awaiting Odyssey Scanning" twice in the Turnaround Time query instead of "Awaiting Lending Scanning"

...

Zen Section
id1357741419

The following Lending Web Reports are available:

Section
Column

 

Column
Column
Zen Section
id1357766982

Fill Rate Statistics

This report shows all of the requests that were received - broken down by articles and loans - for a time period. It also shows the percentage filled, cancelled, conditionalized and still in process for each request type.

Reveal
idLfill-rate-stats
close-otherstrue

Fill Rate Statistics query

Disappear
idLfill-rate-stats
Code Block
linenumberstrue
languagesql
select t.RequestType, t.TransactionStatus, count(t.RequestType) as Number 
from Transactions t, Users u 
WHERE t.Username = u.Username 
AND t.ProcessType = N''Lending'' 
and t.CreationDate BETWEEN @beginDate AND @endDate 
Group By t.RequestType, t.TransactionStatus 
Order By t.RequestType, t.TransactionStatus 
',N'@beginDate datetime,@endDate datetime',@beginDate='2014-07-01 00:00:00',@endDate='2014-08-01 00:00:00
Begin DateThe beginning date of the report. This date is inclusive.
End DateThe end date of the report. This date is not inclusive.
Groups By SiteLimit results in the report based on group affiliation and processing site/delivery location.

...

Zen Section
id912797594

Turnaround Time

This report shows the amount of time required to fill articles, loans and both request types for a given period. It also shows the percentage of time spent by each part of the process. There is an option to turn on details for this report to view every request's turnaround time individually. Turning on details results in a very large report.

Reveal
idLturnaround-time
close-otherstrue

Turnaround Time query

Disappear
idLturnaround-time
Code Block
linenumberstrue
languagesql
SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) as Step4,
MIN(e.[DateTime]) AS Step5
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
JOIN Tracking e ON (t.TransactionNumber = e.TransactionNumber)
WHERE
t.RequestType = 'Article'
AND t.ProcessType = 'Lending'
AND ((a.ChangedTo LIKE 'Imported from%') OR (a.ChangedTo = 'Request Added through Client') OR (a.ChangedTo = 'Awaiting Local Request Processing') OR (a.ChangedTo = 'Awaiting ISO ILL Request Processing') OR (a.ChangedTo = 'Submitted via Lending Web'))
AND b.ChangedTo = 'Request in Processing'
AND c.ChangedTo = 'In Stacks Searching'
AND (d.ChangedTo = 'Awaiting Shipping Label Printing' OR d.ChangedTo = 'Awaiting OdysseyLending Scanning')
AND e.ChangedTo = 'Request Finished'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '2/1/2011'
AND e.[DateTime] < '3/1/2011'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[DateTime]
ORDER BY t.TransactionNumber

SELECT
t.TransactionNumber,
MAX(a.[DateTime]) AS Step1,
MIN(b.[DateTime]) as Step2,
MIN(c.[DateTime]) as Step3,
MIN(d.[DateTime]) as Step4,
MIN(e.[DateTime]) AS Step5
FROM
Transactions t
JOIN Users u ON (t.Username = u.Username)
JOIN Tracking a ON (t.TransactionNumber = a.TransactionNumber)
JOIN Tracking b ON (t.TransactionNumber = b.TransactionNumber)
JOIN Tracking c ON (t.TransactionNumber = c.TransactionNumber)
JOIN Tracking d ON (t.TransactionNumber = d.TransactionNumber)
JOIN Tracking e ON (t.TransactionNumber = e.TransactionNumber)
WHERE
t.RequestType = 'Loan'
AND t.ProcessType = 'Lending'
AND ((a.ChangedTo LIKE 'Imported from%') OR (a.ChangedTo = 'Request Added through Client') OR (a.ChangedTo = 'Awaiting Local Request Processing') OR (a.ChangedTo = 'Awaiting ISO ILL Request Processing') OR (a.ChangedTo = 'Submitted via Lending Web'))
AND b.ChangedTo = 'Request in Processing'
AND c.ChangedTo = 'In Stacks Searching'
AND (d.ChangedTo = 'Awaiting Shipping Label Printing' OR d.ChangedTo = 'Awaiting OdysseyLending Scanning')
AND e.[DateTime] = (SELECT MIN(DateTime) FROM Tracking WHERE TransactionNumber = t.TransactionNumber AND ChangedTo = 'Item Shipped')
AND e.[DateTime] > '2/1/2011 12:00:00 AM'
AND e.[DateTime] < '3/1/2011 12:00:00 AM'
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime], e.[DateTime]
ORDER BY t.TransactionNumber
Begin DateThe beginning date of the report. This date is inclusive.
End DateThe end date of the report. This date is not inclusive.
Show Detailed Records for ArticlesShow detailed records for articles to display every transaction number, specific
times for each step of the process for that transaction, and the overall transaction
turnaround time. Selecting this option will result in a large report.
Show Detailed Records for LoansShow detailed records for loans to display every transaction number, specific
times for each step of the process for that transaction, and the overall transaction
turnaround time. Selecting this option will result in a large report.
Groups By SiteLimit results in a report based on group affiliation and processing site/delivery location.

...