-
Notifications
You must be signed in to change notification settings - Fork 0
/
MainCountB.sql
263 lines (241 loc) · 82.3 KB
/
MainCountB.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
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
Select
Count (case when item.location_code = 'gmad1' and (call.index_entry between '000' and '099.9999' or itemcall.call_number between '000' and '099.9999') and (call.index_entry not between '004' and '006.9999') and (itemcall.call_number not between '004' and '069.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "000-099 0",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '004' and '006.9999' or itemcall.call_number between '004' and '006.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "004-006 1",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '100' and '199.9999' or itemcall.call_number between '100' and '149.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "100-199 2",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '200' and '299.9999' or itemcall.call_number between '200' and '299.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "200-299 3",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '720' and '729.9999' or itemcall.call_number between '720' and '729.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Architecture 720-729 4",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '700' and '709.9999' or itemcall.call_number between '700' and '709.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "700-709 Art 5",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '730' and '739.9999' or itemcall.call_number between '730' and '739.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "730-739 Art 6",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '750' and '769.9999' or itemcall.call_number between '750' and '769.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "750-769 Art 7",
Count (case when item.location_code = 'gmad2' and item.itype_code_num = '22' and call.index_entry like 'comp%' and itemcall.call_number not like 'J COMP DISC%' and itemcall.call_number not like 'YA COMP DISC%'then 1 end) as "Audiobook 8",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '920' and '929.9999' or itemcall.call_number between '920' and '929.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Biography Genealogy 920-929 9",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '660' and '699.9999' or itemcall.call_number between '660' and '699.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "660-699 Building 10",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '330' and '339.9999' or itemcall.call_number between '330' and '339.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "330-339 Business 11",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '650' and '659.9999' or itemcall.call_number between '650' and '659.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "650-659 Business 12",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '649' and '649.9999' or itemcall.call_number between '649' and '649.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "649 13",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'chi%' or itemcall.call_number like 'CHI%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Chinese 14",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '640' and '642.9999' or itemcall.call_number between '640' and '642.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "640-642 15",
Count (case when item.location_code = 'gmad2' and (call.index_entry between '793.3' and '793.39999' or itemcall.call_number between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793 16",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '747' and '749.9999' or itemcall.call_number between '747' and '749.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "747-749 17",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '742' and '745.49999' or itemcall.call_number between '742' and '745.49999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "742-745.4 18",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '370' and '379.9999' or itemcall.call_number between '370' and '379.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "370-379 19",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '620' and '629.9999' or itemcall.call_number between '620' and '629.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "620-619 20",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '800' and '829.9999' or itemcall.call_number between '800' and '829.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "800-829 21",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '390' and '399.9999' or itemcall.call_number between '390' and '399.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "390-399 22",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'dvd fe%' or itemcall.call_number like 'DVD FE%') and itemcall.call_number not like 'J DVD%' then 1 end) as "Adult DVDs 23",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'fiction%' or itemcall.call_number like 'FICT%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK %' and itemcall.call_number not like 'EXPRE%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "FICTION 24",
Count (case when item.location_code = 'gmad1' and (call.index_entry like 'pbk f%' or itemcall.call_number like 'PBK F%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Fiction 25",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '790' and '791.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "790-791 26",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'fre%' or itemcall.call_number like 'FRE%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "French 27",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '793' and '795.9999' or itemcall.call_number between '793' and '795.9999') and (call.index_entry not between '793.3' and '793.39999') and (itemcall.call_number not between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793-795 28",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '630' and '635.9999' or itemcall.call_number between '630' and '635.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "630-635 29",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '900' and '909.9999' or itemcall.call_number between '900' and '909.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "900-909 30",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'ger%' or itemcall.call_number like 'GER%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "German 31",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'graphic%' or itemcall.call_number like 'GRAPHIC%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "GRAPHIC NOVEL 32",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '745.5' and '746.9999' or itemcall.call_number between '745.5' and '746.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "745.5-746 33",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '362' and '362.9999' or itemcall.call_number between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "362 34",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '610' and '619.9999' or itemcall.call_number between '610' and '619.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "610-619 35",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '643' and '648.9999' or itemcall.call_number between '643' and '648.9999') and (call.index_entry not between '647' and '647.99999') and (itemcall.call_number not between '647' and '647.9999')and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "643-648 36",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '740' and '741.9999' or itemcall.call_number between '740' and '741.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "740-741 37",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'ita%' or itemcall.call_number like 'ITA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Italian 38",
Count (case when item.location_code = 'gmwor' and ((call.index_entry like 'jpn%' or call.index_entry like 'japan%') or (itemcall.call_number like 'JPN%' or itemcall.call_number like 'JAPAN%')) and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Japanese 39",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '710' and '719.9999' or itemcall.call_number between '710' and '719.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "710-719 40",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'langu%' or itemcall.call_number like 'LANG%') and itemcall.call_number not like 'J%' then 1 end) as "Language Learning 41",
Count (case when item.location_code = 'gmwor' and (call.index_entry between '400' and '499.9999' or itemcall.call_number between '400' and '499.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "400-499 42",
Count (case when item.location_code = 'gmad1' and call.index_entry like 'lt%' and call.index_entry not like 'lt mag%' then 1 end) as "Large print 44",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '340' and '349.9999' or itemcall.call_number between '340' and '349.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "340-349 43",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '974.6' and '974.69999' or itemcall.call_number between '974.6' and '974.69999') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 45",
Count (case when item.location_code = 'gmad2' and (call.index_entry between '780' and '789.9999' or itemcall.call_number between '780' and '789.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "780-789 46",
Count (case when item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc box%' and itemcall.call_number not like 'J COMP DISC%') then 1 end) as "Comp Disc BOX 47",
Count (case when item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc%' and itemcall.call_number not like 'J COMP DISC%' and call.index_entry not like 'comp disc box%') then 1 end) as "Music CDS 48",
Count (case when item.location_code = 'gmad2' and item.itype_code_num = '41' and (call.index_entry between 'dvd 780' and 'dvd 789.99999' or itemcall.call_number between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Music DVDs 49",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'music score%' or call.index_entry like 'libr%') then 1 end) as "Music Score 50",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'mystery%' or itemcall.call_number like 'MYSTERY%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "MYSTERY 51",
Count (case when item.location_code = 'gmad1' and (call.index_entry like 'pbk m%' or itemcall.call_number like 'PBK M%') and itemcall.call_number not like 'MYST%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Mystery 52",
Count (case when item.location_code = 'gmnew' and (call.index_entry not between '000' and '999.9999') and (itemcall.call_number not between '000' and '999.9999') then 1 end) as "New FIC/MYS/SCI/ShSt/GraphicN 53",
Count (case when item.location_code = 'gmnew' and ((call.index_entry between '000' and '999.9999') or (itemcall.call_number between '000' and '999.9999')) then 1 end) as "New Non-Fict 54",
Count (case when item.location_code = 'gmad2' and item.itype_code_num = '41' and call.index_entry not like 'dvd fe%' and itemcall.call_number not like 'DVD FE%' and (call.index_entry not between 'dvd 780' and 'dvd 789.99999') and (itemcall.call_number not between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Non-Fiction DVDs 55",
Count (case when item.location_code = 'gmad1' and (call.index_entry like 'pbk non%' or itemcall.call_number like 'PBK NON%') and itemcall.call_number not between '000' and '999.9999' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Non-Fiction 56",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '970' and '999.9999' or itemcall.call_number between '970' and '999.9999') and (call.index_entry not between '974.6' and '974.69999') and (itemcall.call_number not between '974.6' and '974.699999')and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 57",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '600' and '609.9999' or itemcall.call_number between '600' and '609.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "600-609 58",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '636' and '639.9999' or itemcall.call_number between '636' and '639.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "636-639 59",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '770' and '779.9999' or itemcall.call_number between '770' and '779.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "770-779 60",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '320' and '329.9999' or itemcall.call_number between '320' and '329.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "320-329 61",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '350' and '359.9999' or itemcall.call_number between '350' and '359.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "350-359 62",
Count (case when item.location_code = 'gmad1' and (call.index_entry like 'pbk r%' or itemcall.call_number like 'PBK R%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Romance 63",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '500' and '599.9999' or itemcall.call_number between '500' and '599.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "500-599,310-319 64",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'scifi%' or itemcall.call_number like 'SCIFI%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SCIFI 65",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'short%' or itemcall.call_number like 'SHORT%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SHORT STORY 66",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '360' and '369.9999' or itemcall.call_number between '360' and '369.9999') and (call.index_entry not between '362' and '362.9999') and (itemcall.call_number not between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "360-369 67",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '300' and '319.9999' or itemcall.call_number between '300' and '319.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "300-319 68",
Count (case when item.location_code = 'gmwor' and (call.index_entry like 'spa%' or itemcall.call_number like 'SPA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%'then 1 end) as "Spanish 68",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '796' and '799.9999' or itemcall.call_number between '796' and '799.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "796-799 69",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '792' and '792.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "792 70",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '380' and '389.9999' or itemcall.call_number between '380' and '389.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "380-389 71",
Count (case when item.location_code = 'gmad1' and ((call.index_entry between '910' and '919.9999' or itemcall.call_number between '910' and '919.9999') or (call.index_entry between '647' and '647.9999' or itemcall.call_number between '647' and '647.9999')) and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "910-919 72",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '930' and '969.9999' or itemcall.call_number between '930' and '969.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "930-969 73",
Count (case when item.location_code = 'gmad1' and (call.index_entry between '830' and '899.9999' or itemcall.call_number between '830' and '899.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "830-899 74",
Count (case when item.location_code = 'gmad2' and (call.index_entry like 'ya%' or itemcall.call_number like 'YA%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'FIC%' and itemcall.call_number not like 'GRAPH%' and itemcall.call_number not like 'MYS%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' then 1 end) as "YA 75",
--checked out
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '000' and '099.9999' or itemcall.call_number between '000' and '099.9999') and (call.index_entry not between '004' and '006.9999') and (itemcall.call_number not between '004' and '069.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "000-099 0B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '004' and '006.9999' or itemcall.call_number between '004' and '006.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "004-006 1B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '100' and '199.9999' or itemcall.call_number between '100' and '149.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "100-199 2B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '200' and '299.9999' or itemcall.call_number between '200' and '299.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "200-299 3B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '720' and '729.9999' or itemcall.call_number between '720' and '729.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Architecture 720-729 4B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '700' and '709.9999' or itemcall.call_number between '700' and '709.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "700-709 Art 5B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '730' and '739.9999' or itemcall.call_number between '730' and '739.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "730-739 Art 6B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '750' and '769.9999' or itemcall.call_number between '750' and '769.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "750-769 Art 7B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and item.itype_code_num = '22' and call.index_entry like 'comp%' and itemcall.call_number not like 'J COMP DISC%' and itemcall.call_number not like 'YA COMP DISC%'then 1 end) as "Audiobook 8B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '920' and '929.9999' or itemcall.call_number between '920' and '929.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Biography Genealogy 920-929 9B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '660' and '699.9999' or itemcall.call_number between '660' and '699.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "660-699 Building 10B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '330' and '339.9999' or itemcall.call_number between '330' and '339.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "330-339 Business 11B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '650' and '659.9999' or itemcall.call_number between '650' and '659.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "650-659 Business 12B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '649' and '649.9999' or itemcall.call_number between '649' and '649.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "649 13B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'chi%' or itemcall.call_number like 'CHI%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Chinese 14B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '640' and '642.9999' or itemcall.call_number between '640' and '642.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "640-642 15B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry between '793.3' and '793.39999' or itemcall.call_number between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793 16B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '747' and '749.9999' or itemcall.call_number between '747' and '749.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "747-749 17B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '742' and '745.49999' or itemcall.call_number between '742' and '745.49999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "742-745.4 18B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '370' and '379.9999' or itemcall.call_number between '370' and '379.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "370-379 19B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '620' and '629.9999' or itemcall.call_number between '620' and '629.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "620-619 20B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '800' and '829.9999' or itemcall.call_number between '800' and '829.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "800-829 21B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '390' and '399.9999' or itemcall.call_number between '390' and '399.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "390-399 22B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'dvd fe%' or itemcall.call_number like 'DVD FE%') and itemcall.call_number not like 'J DVD%' then 1 end) as "Adult DVDs 23B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'fiction%' or itemcall.call_number like 'FICT%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK %' and itemcall.call_number not like 'EXPRE%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "FICTION 24B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry like 'pbk f%' or itemcall.call_number like 'PBK F%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Fiction 25B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '790' and '791.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "790-791 26B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'fre%' or itemcall.call_number like 'FRE%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "French 27B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '793' and '795.9999' or itemcall.call_number between '793' and '795.9999') and (call.index_entry not between '793.3' and '793.39999') and (itemcall.call_number not between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793-795 28B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '630' and '635.9999' or itemcall.call_number between '630' and '635.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "630-635 29B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '900' and '909.9999' or itemcall.call_number between '900' and '909.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "900-909 30B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'ger%' or itemcall.call_number like 'GER%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "German 31B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'graphic%' or itemcall.call_number like 'GRAPHIC%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "GRAPHIC NOVEL 32B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '745.5' and '746.9999' or itemcall.call_number between '745.5' and '746.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "745.5-746 33B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '362' and '362.9999' or itemcall.call_number between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "362 34B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '610' and '619.9999' or itemcall.call_number between '610' and '619.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "610-619 35B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '643' and '648.9999' or itemcall.call_number between '643' and '648.9999') and (call.index_entry not between '647' and '647.99999') and (itemcall.call_number not between '647' and '647.9999')and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "643-648 36B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '740' and '741.9999' or itemcall.call_number between '740' and '741.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "740-741 37B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'ita%' or itemcall.call_number like 'ITA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Italian 38B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and ((call.index_entry like 'jpn%' or call.index_entry like 'japan%') or (itemcall.call_number like 'JPN%' or itemcall.call_number like 'JAPAN%')) and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Japanese 39B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '710' and '719.9999' or itemcall.call_number between '710' and '719.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "710-719 40B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'langu%' or itemcall.call_number like 'LANG%') and itemcall.call_number not like 'J%' then 1 end) as "Language Learning 41B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry between '400' and '499.9999' or itemcall.call_number between '400' and '499.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "400-499 42B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '340' and '349.9999' or itemcall.call_number between '340' and '349.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "340-349 43B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and call.index_entry like 'lt%' and call.index_entry not like 'lt mag%' then 1 end) as "Large print 44B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '974.6' and '974.69999' or itemcall.call_number between '974.6' and '974.69999') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 45B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry between '780' and '789.9999' or itemcall.call_number between '780' and '789.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "780-789 46B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc box%' and itemcall.call_number not like 'J COMP DISC%') then 1 end) as "Comp Disc BOX 47B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc%' and itemcall.call_number not like 'J COMP DISC%' and call.index_entry not like 'comp disc box%') then 1 end) as "Music CDS 48B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and item.itype_code_num = '41' and (call.index_entry between 'dvd 780' and 'dvd 789.99999' or itemcall.call_number between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Music DVDs 49B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'music score%' or call.index_entry like 'libr%' or itemcall.call_number like 'LIB%') then 1 end) as "Music Score 50B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'mystery%' or itemcall.call_number like 'MYSTERY%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "MYSTERY 51B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry like 'pbk m%' or itemcall.call_number like 'PBK M%') and itemcall.call_number not like 'MYST%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Mystery 52B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmnew' and (call.index_entry not between '000' and '999.9999') and (itemcall.call_number not between '000' and '999.9999') then 1 end) as "New FIC/MYS/SCI/ShSt/GraphicN 53B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmnew' and ((call.index_entry between '000' and '999.9999') or (itemcall.call_number between '000' and '999.9999')) then 1 end) as "New Non-Fict 54B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and item.itype_code_num = '41' and call.index_entry not like 'dvd fe%' and itemcall.call_number not like 'DVD FE%' and (call.index_entry not between 'dvd 780' and 'dvd 789.99999') and (itemcall.call_number not between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Non-Fiction DVDs 55B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry like 'pbk non%' or itemcall.call_number like 'PBK NON%') and itemcall.call_number not between '000' and '999.9999' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Non-Fiction 56B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '970' and '999.9999' or itemcall.call_number between '970' and '999.9999') and (call.index_entry not between '974.6' and '974.69999') and (itemcall.call_number not between '974.6' and '974.699999')and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 57B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '600' and '609.9999' or itemcall.call_number between '600' and '609.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "600-609 58B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '636' and '639.9999' or itemcall.call_number between '636' and '639.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "636-639 59B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '770' and '779.9999' or itemcall.call_number between '770' and '779.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "770-779 60B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '320' and '329.9999' or itemcall.call_number between '320' and '329.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "320-329 61B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '350' and '359.9999' or itemcall.call_number between '350' and '359.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "350-359 62B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry like 'pbk r%' or itemcall.call_number like 'PBK R%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Romance 63B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '500' and '599.9999' or itemcall.call_number between '500' and '599.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "500-599,310-319 64B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'scifi%' or itemcall.call_number like 'SCIFI%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SCIFI 65B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'short%' or itemcall.call_number like 'SHORT%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SHORT STORY 66B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '360' and '369.9999' or itemcall.call_number between '360' and '369.9999') and (call.index_entry not between '362' and '362.9999') and (itemcall.call_number not between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "360-369 67B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '300' and '319.9999' or itemcall.call_number between '300' and '319.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "300-319 68B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmwor' and (call.index_entry like 'spa%' or itemcall.call_number like 'SPA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%'then 1 end) as "Spanish 68B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '796' and '799.9999' or itemcall.call_number between '796' and '799.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "796-799 69B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '792' and '792.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "792 70B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '380' and '389.9999' or itemcall.call_number between '380' and '389.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "380-389 71B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and ((call.index_entry between '910' and '919.9999' or itemcall.call_number between '910' and '919.9999') or (call.index_entry between '647' and '647.9999' or itemcall.call_number between '647' and '647.9999')) and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "910-919 72B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '930' and '969.9999' or itemcall.call_number between '930' and '969.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "930-969 73B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad1' and (call.index_entry between '830' and '899.9999' or itemcall.call_number between '830' and '899.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "830-899 74B",
Count (case when cout.loanrule_code_num > '0' and item.location_code = 'gmad2' and (call.index_entry like 'ya%' or itemcall.call_number like 'YA%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'FIC%' and itemcall.call_number not like 'GRAPH%' and itemcall.call_number not like 'MYS%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' then 1 end) as "YA 75",
--status not available
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '000' and '099.9999' or itemcall.call_number between '000' and '099.9999') and (call.index_entry not between '004' and '006.9999') and (itemcall.call_number not between '004' and '069.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "000-099 0C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '004' and '006.9999' or itemcall.call_number between '004' and '006.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "004-006 1C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '100' and '199.9999' or itemcall.call_number between '100' and '149.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "100-199 2C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '200' and '299.9999' or itemcall.call_number between '200' and '299.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "200-299 3C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '720' and '729.9999' or itemcall.call_number between '720' and '729.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Architecture 720-729 4C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '700' and '709.9999' or itemcall.call_number between '700' and '709.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "700-709 Art 5C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '730' and '739.9999' or itemcall.call_number between '730' and '739.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "730-739 Art 6C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '750' and '769.9999' or itemcall.call_number between '750' and '769.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "750-769 Art 7C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and item.itype_code_num = '22' and call.index_entry like 'comp%' and itemcall.call_number not like 'J COMP DISC%' and itemcall.call_number not like 'YA COMP DISC%'then 1 end) as "Audiobook 8C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '920' and '929.9999' or itemcall.call_number between '920' and '929.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "Biography Genealogy 920-929 9C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '660' and '699.9999' or itemcall.call_number between '660' and '699.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "660-699 Building 10C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '330' and '339.9999' or itemcall.call_number between '330' and '339.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "330-339 Business 11C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '650' and '659.9999' or itemcall.call_number between '650' and '659.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "650-659 Business 12C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '649' and '649.9999' or itemcall.call_number between '649' and '649.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "649 13C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'chi%' or itemcall.call_number like 'CHI%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Chinese 14C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '640' and '642.9999' or itemcall.call_number between '640' and '642.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "640-642 15C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry between '793.3' and '793.39999' or itemcall.call_number between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793 16C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '747' and '749.9999' or itemcall.call_number between '747' and '749.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "747-749 17C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '742' and '745.49999' or itemcall.call_number between '742' and '745.49999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "742-745.4 18C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '370' and '379.9999' or itemcall.call_number between '370' and '379.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "370-379 19C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '620' and '629.9999' or itemcall.call_number between '620' and '629.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "620-619 20C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '800' and '829.9999' or itemcall.call_number between '800' and '829.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "800-829 21C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '390' and '399.9999' or itemcall.call_number between '390' and '399.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "390-399 22C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'dvd fe%' or itemcall.call_number like 'DVD FE%') and itemcall.call_number not like 'J DVD%' then 1 end) as "Adult DVDs 23C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'fiction%' or itemcall.call_number like 'FICT%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK %' and itemcall.call_number not like 'EXPRE%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "FICTION 24C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry like 'pbk f%' or itemcall.call_number like 'PBK F%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Fiction 25C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '790' and '791.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "790-791 26C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'fre%' or itemcall.call_number like 'FRE%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "French 27C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '793' and '795.9999' or itemcall.call_number between '793' and '795.9999') and (call.index_entry not between '793.3' and '793.39999') and (itemcall.call_number not between '793.3' and '793.39999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "793-795 28C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '630' and '635.9999' or itemcall.call_number between '630' and '635.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "630-635 29C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '900' and '909.9999' or itemcall.call_number between '900' and '909.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "900-909 30C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'ger%' or itemcall.call_number like 'GER%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "German 31C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'graphic%' or itemcall.call_number like 'GRAPHIC%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' then 1 end) as "GRAPHIC NOVEL 32C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '745.5' and '746.9999' or itemcall.call_number between '745.5' and '746.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "745.5-746 33C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '362' and '362.9999' or itemcall.call_number between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "362 34C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '610' and '619.9999' or itemcall.call_number between '610' and '619.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "610-619 35C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '643' and '648.9999' or itemcall.call_number between '643' and '648.9999') and (call.index_entry not between '647' and '647.99999') and (itemcall.call_number not between '647' and '647.9999')and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "643-648 36C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '740' and '741.9999' or itemcall.call_number between '740' and '741.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "740-741 37C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'ita%' or itemcall.call_number like 'ITA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Italian 38C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and ((call.index_entry like 'jpn%' or call.index_entry like 'japan%') or (itemcall.call_number like 'JPN%' or itemcall.call_number like 'JAPAN%')) and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%' then 1 end) as "Japanese 39C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '710' and '719.9999' or itemcall.call_number between '710' and '719.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "710-719 40C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'langu%' or itemcall.call_number like 'LANG%') and itemcall.call_number not like 'J%' then 1 end) as "Language Learning 41C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry between '400' and '499.9999' or itemcall.call_number between '400' and '499.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "400-499 42C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '340' and '349.9999' or itemcall.call_number between '340' and '349.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "340-349 43C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and call.index_entry like 'lt%' and call.index_entry not like 'lt mag%' then 1 end) as "Large print 44C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '974.6' and '974.69999' or itemcall.call_number between '974.6' and '974.69999') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 45C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry between '780' and '789.9999' or itemcall.call_number between '780' and '789.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "780-789 46C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc box%' and itemcall.call_number not like 'J COMP DISC%') then 1 end) as "Comp Disc BOX 47C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and item.itype_code_num = '2' and (call.index_entry like 'comp disc%' and itemcall.call_number not like 'J COMP DISC%' and call.index_entry not like 'comp disc box%') then 1 end) as "Music CDS 48C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and item.itype_code_num = '41' and (call.index_entry between 'dvd 780' and 'dvd 789.99999' or itemcall.call_number between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Music DVDs 49C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'music score%' or call.index_entry like 'libr%' or itemcall.call_number like 'LIB%') then 1 end) as "Music Score 50C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'mystery%' or itemcall.call_number like 'MYSTERY%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "MYSTERY 51C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry like 'pbk m%' or itemcall.call_number like 'PBK M%') and itemcall.call_number not like 'MYST%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Mystery 52C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmnew' and (call.index_entry not between '000' and '999.9999') and (itemcall.call_number not between '000' and '999.9999') then 1 end) as "New FIC/MYS/SCI/ShSt/GraphicN 53C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmnew' and ((call.index_entry between '000' and '999.9999') or (itemcall.call_number between '000' and '999.9999')) then 1 end) as "New Non-Fict 54C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and item.itype_code_num = '41' and call.index_entry not like 'dvd fe%' and itemcall.call_number not like 'DVD FE%' and (call.index_entry not between 'dvd 780' and 'dvd 789.99999') and (itemcall.call_number not between 'DVD 780' and 'DVD 789.99999') and itemcall.call_number not like 'J DVD%' then 1 end) as "Non-Fiction DVDs 55C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry like 'pbk non%' or itemcall.call_number like 'PBK NON%') and itemcall.call_number not between '000' and '999.9999' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Non-Fiction 56C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '970' and '999.9999' or itemcall.call_number between '970' and '999.9999') and (call.index_entry not between '974.6' and '974.69999') and (itemcall.call_number not between '974.6' and '974.699999')and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' then 1 end) as "970-999 57C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '600' and '609.9999' or itemcall.call_number between '600' and '609.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "600-609 58C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '636' and '639.9999' or itemcall.call_number between '636' and '639.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "636-639 59C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '770' and '779.9999' or itemcall.call_number between '770' and '779.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "770-779 60C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '320' and '329.9999' or itemcall.call_number between '320' and '329.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "320-329 61C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '350' and '359.9999' or itemcall.call_number between '350' and '359.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "350-359 62C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry like 'pbk r%' or itemcall.call_number like 'PBK R%') and itemcall.call_number not like 'FICT%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "Paperback Romance 63C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '500' and '599.9999' or itemcall.call_number between '500' and '599.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "500-599,310-319 64C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'scifi%' or itemcall.call_number like 'SCIFI%') and itemcall.call_number not like 'GRAP%' and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SCIFI 65C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'short%' or itemcall.call_number like 'SHORT%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'J%' and itemcall.call_number not like 'YA%' then 1 end) as "SHORT STORY 66C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '360' and '369.9999' or itemcall.call_number between '360' and '369.9999') and (call.index_entry not between '362' and '362.9999') and (itemcall.call_number not between '362' and '362.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "360-369 67C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '300' and '319.9999' or itemcall.call_number between '300' and '319.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "300-319 68C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmwor' and (call.index_entry like 'spa%' or itemcall.call_number like 'SPA%') and itemcall.call_number not like 'J%' and itemcall.call_number not like 'E%'then 1 end) as "Spanish 68C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '796' and '799.9999' or itemcall.call_number between '796' and '799.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "796-799 69C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '792' and '792.9999' or itemcall.call_number between '790' and '791.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "792 70C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '380' and '389.9999' or itemcall.call_number between '380' and '389.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "380-389 71C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and ((call.index_entry between '910' and '919.9999' or itemcall.call_number between '910' and '919.9999') or (call.index_entry between '647' and '647.9999' or itemcall.call_number between '647' and '647.9999')) and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "910-919 72C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '930' and '969.9999' or itemcall.call_number between '930' and '969.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "930-969 73C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad1' and (call.index_entry between '830' and '899.9999' or itemcall.call_number between '830' and '899.9999') and itemcall.call_number not like 'YA%' and itemcall.call_number not like 'J%' and itemcall.call_number <> 'PBK NONFICTION' and itemcall.call_number not like 'REF%' then 1 end) as "830-899 74C",
Count (case when item.item_status_code <> '-' and item.item_status_code <> 'n' and item.location_code = 'gmad2' and (call.index_entry like 'ya%' or itemcall.call_number like 'YA%') and itemcall.call_number not like 'PBK%' and itemcall.call_number not like 'FIC%' and itemcall.call_number not like 'GRAPH%' and itemcall.call_number not like 'MYS%' and itemcall.call_number not like 'SCI%' and itemcall.call_number not like 'J%' then 1 end) as "YA 75"
from
sierra_view.checkout as cout
right join sierra_view.item_view as item on item.id = cout.item_record_id
inner join sierra_view.item_record_property as itemcall on itemcall.item_record_id = item.id
join sierra_view.bib_record_item_record_link as link on link.item_record_id = item.id
join sierra_view.phrase_entry as call on call.record_id = link.bib_record_id
where call.varfield_type_code = 'c';