If I want to Limit the Doc Del or Borrowing Turnaround Time Report for only requests sent through Automated Request Manager, how would I do that?

Applies to

• ILLiad

Answer

Find the following portion of the SQL query:

AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR

(b.ChangedTo = 'Awaiting OCLC Sending'))

And, substitute the following line:

AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending'))

Find the following portion of the SQL query:

AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending') OR

(b.ChangedTo = 'Awaiting Rapid Request Sending') OR (b.ChangedTo = 'Awaiting OCLC Sending'))

And, substitute the following line:

AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending'))

The updated SQL query should look like the following:

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 = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo = 'In Electronic Delivery Processing') OR (d.ChangedTo = 'Awaiting Odyssey Processing') OR 
(d.ChangedTo = 'Awaiting Post Receipt Processing'))
AND ((e.ChangedTo LIKE 'Customer%Notified%') OR (e.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '7/1/2020'
AND e.[DateTime] < '7/1/2021'
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 = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo = 'In Electronic Delivery Processing') OR (d.ChangedTo = 'Awaiting Odyssey Processing') OR 
(d.ChangedTo = 'Awaiting Post Receipt Processing'))
AND ((e.ChangedTo LIKE 'Customer%Notified%') OR (e.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND e.[DateTime] > d.[DateTime]
AND e.[DateTime] > '7/1/2020 12:00:00 AM'
AND e.[DateTime] < '7/1/2021 12:00:00 AM'
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
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)
WHERE
t.ProcessType = 'Borrowing'
AND ((a.ChangedTo LIKE '%Submitted by Customer') OR (a.ChangedTo = 'Request Added through Client') OR 
(a.ChangedTo like 'Imported from%'))
AND ((b.ChangedTo = 'Request in Processing') OR (b.ChangedTo = 'Awaiting Direct Request Sending'))
AND c.ChangedTo = 'Request Sent'
AND ((d.ChangedTo LIKE 'Customer%Notified%') OR (d.ChangedTo = 'Delivered to Web'))
AND b.[DateTime] > a.[DateTime]
AND c.[DateTime] > b.[DateTime]
AND d.[DateTime] > c.[DateTime]
AND d.[DateTime] > '7/1/2020 12:00:00 AM'
AND d.[DateTime] < '7/1/2021 12:00:00 AM'
GROUP BY t.TransactionNumber, a.[DateTime], b.[DateTime], c.[DateTime], d.[DateTime]
ORDER BY t.TransactionNumber

Page ID