-
Notifications
You must be signed in to change notification settings - Fork 5
/
Overdue holds.sql
115 lines (69 loc) · 2.55 KB
/
Overdue holds.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- SSMS or Report Builder
select o.Abbreviation
[Checkout Org]
, p.Barcode
[PatronBarcode]
, pr.PatronFullName
, ic.DueDate
, do.DeliveryOption
, case when pr.DeliveryOptionID
= 3 then pr.PhoneVoice1
when pr.DeliveryOptionID
= 4 then pr.PhoneVoice2
when pr.DeliveryOptionID
= 5 then pr.PhoneVoice3
else
case when pr.TxtPhoneNumber = 1 then pr.PhoneVoice1
when pr.TxtPhoneNumber
= 2 then pr.PhoneVoice2
when pr.TxtPhoneNumber
= 3 then pr.PhoneVoice3
else COALESCE(pr.PhoneVoice1, pr.PhoneVoice2, pr.PhoneVoice3) + ' : First #' end
end [PhoneNumber]
, br.BrowseTitle
, br.BrowseAuthor
, cir.Barcode
[ItemBarcode]
from polaris.ItemCheckouts ic (nolock)
inner join polaris.CircItemRecords cir (nolock) on cir.ItemRecordID = ic.ItemRecordID
inner join polaris.Patrons p (nolock) on p.PatronID = ic.PatronID
inner join polaris.PatronRegistration pr (nolock) on pr.PatronID = ic.PatronID
inner join polaris.BibliographicRecords br (nolock) on br.BibliographicRecordID =
cir.AssociatedBibRecordID
left outer join polaris.DeliveryOptions
do (nolock) on do.DeliveryOptionID
= pr.DeliveryOptionID
inner join polaris.Organizations o (nolock) on o.OrganizationID = ic.OrganizationID
where cir.AssociatedBibRecordID
IN
(
select shr.BibliographicRecordID
from polaris.SysHoldRequests
shr (nolock) where shr.SysHoldStatusID
= 3 and (shr.ItemLevelHoldItemRecordID =
ic.ItemRecordID or
shr.ItemLevelHoldItemRecordID is null)
)
and ic.DueDate < getdate()
--and p.PatronCodeID IN (1) -- if you want to limit to specific patron
codes --
select * from polaris.patroncodes (nolock)
--and ic.OrganizationID IN (3) -- optional branch limiter --
select * from polaris.organizations (nolock)
order by o.Abbreviation, pr.PatronFullName, ic.DueDate, br.BrowseTitle, br.BrowseAuthor, cir.Barcode -- Choose your own ordering that works best
-- Find Tool (Item Records)
select distinct cir.ItemRecordID
from polaris.ItemCheckouts ic (nolock)
inner join polaris.CircItemRecords cir (nolock) on cir.ItemRecordID = ic.ItemRecordID
where cir.AssociatedBibRecordID
IN
(
select shr.BibliographicRecordID
from polaris.SysHoldRequests
shr (nolock) where shr.SysHoldStatusID
= 3 and (shr.ItemLevelHoldItemRecordID =
ic.ItemRecordID or
shr.ItemLevelHoldItemRecordID is null)
)
and ic.DueDate < getdate()
and ic.OrganizationID in (3)