-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathHoldsOnBibsWithItemsMarkedMissing.sql
172 lines (171 loc) · 3.47 KB
/
HoldsOnBibsWithItemsMarkedMissing.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
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
SELECT
concat(
rmb.record_type_code,
rmb.record_num,
'a'
) AS "Bib Number",
bprop.best_title As "Title",
CASE
WHEN rmp1.record_num IS NULL
THEN concat(
rmp2.record_type_code,
rmp2.record_num,
'a'
)
ELSE concat(
rmp1.record_type_code,
rmp2.record_num,
'a'
)
END AS "Patron Number",
CASE
WHEN TRIM(pnam1.last_name) IS NULL
THEN TRIM(pnam2.first_name||' '||pnam2.last_name)
ELSE TRIM(pnam1.first_name||' '||pnam1.last_name)
END AS "Patron Name",
CASE
WHEN hld1.placed_gmt IS NULL
THEN DATE(hld2.placed_gmt)
ELSE DATE(hld1.placed_gmt)
END AS "Hold Placed",
CASE
WHEN hld1.expires_gmt IS NULL
THEN DATE(hld2.expires_gmt)
ELSE DATE(hld1.expires_gmt)
END AS "Hold Expires",
CASE
WHEN hld1.is_frozen IS NULL
AND hld2.is_frozen IS TRUE THEN 'Yes'
WHEN hld1.is_frozen IS NULL
AND hld2.is_frozen IS FALSE THEN 'No'
WHEN hld2.is_frozen IS NULL
AND hld1.is_frozen IS TRUE THEN 'Yes'
WHEN hld2.is_frozen IS NULL
AND hld1.is_frozen IS FALSE THEN 'No'
ELSE 'error'
END AS "Frozen",
CASE
WHEN pulnam1.name IS NULL
THEN pulnam2.name
ELSE pulnam1.name
END AS "Pickup Location",
concat(
rmi.record_type_code,
rmi.record_num,
'a'
) AS "Item Number",
lnam.name AS "Item Location",
isnam.name AS "Item Status"
FROM
sierra_view.bib_record_item_record_link AS bil
JOIN
sierra_view.bib_record_property AS bprop
ON
bprop.bib_record_id = bil.bib_record_id
JOIN
sierra_view.record_metadata AS rmb
ON
rmb.id = bil.bib_record_id
JOIN
sierra_view.record_metadata As rmi
ON
rmi.id = bil.item_record_id
JOIN
sierra_view.item_record AS i
ON
i.record_id = bil.item_record_id
JOIN
sierra_view.location_myuser AS lnam
ON
lnam.code = i.location_code
JOIN
sierra_view.item_status_property_myuser AS isnam
ON
isnam.code = i.item_status_code
LEFT JOIN
sierra_view.hold AS hld1
ON
hld1.record_id = bil.bib_record_id
LEFT JOIN
sierra_view.hold AS hld2
ON
hld2.record_id = bil.item_record_id
LEFT JOIN
sierra_view.patron_record_fullname AS pnam1
ON
pnam1.patron_record_id = hld1.patron_record_id
LEFT JOIN
sierra_view.patron_record_fullname As pnam2
ON
pnam2.patron_record_id = hld2.patron_record_id
LEFT JOIN
sierra_view.record_metadata AS rmp1
ON
rmp1.id = hld1.patron_record_id
LEFT JOIN
sierra_view.record_metadata AS rmp2
ON
rmp2.id = hld2.patron_record_id
LEFT JOIN
sierra_view.location_myuser AS pulnam1
ON
pulnam1.code = hld1.pickup_location_code
LEFT JOIN
sierra_view.location_myuser AS pulnam2
ON
pulnam2.code = hld2.pickup_location_code
WHERE
bil.bib_record_id IN (
SELECT
s2_bil.bib_record_id
FROM
sierra_view.bib_record_item_record_link AS s2_bil
WHERE
s2_bil.bib_record_id IN (
SELECT
s1_bil.bib_record_id
FROM
sierra_view.hold As s1_hld
JOIN
sierra_view.item_record AS s1_i
ON
s1_i.record_id = s1_hld.record_id
AND
s1_i.item_status_code = 'm'
JOIN
sierra_view.bib_record_item_record_link AS s1_bil
ON
s1_bil.item_record_id = s1_hld.record_id
)
GROUP BY
s2_bil.bib_record_id
HAVING
COUNT(s2_bil.item_record_id) = 1
)
OR
bil.bib_record_id IN (
SELECT
s4_bil.bib_record_id
FROM
sierra_view.bib_record_item_record_link AS s4_bil
JOIN
sierra_view.item_record AS s4_i
ON
s4_i.record_id = s4_bil.item_record_id
WHERE
s4_bil.bib_record_id IN (
SELECT
s3_bil.bib_record_id
FROM
sierra_view.hold AS s3_hld
JOIN
sierra_view.bib_record_item_record_link AS s3_bil
ON
s3_bil.bib_record_id = s3_hld.record_id
GROUP By
s3_bil.bib_record_id
HAVING
COUNT(s3_bil.item_record_id) = 1)
AND
s4_i.item_status_code = 'm')
;