API tools faq
paste
View difference between Paste ID: 5V8FTquz and HMd6wNsz
SHOW: | | - or go back to the newest paste.
1
/*
2
3-
Section F LIBRARY COLLECTIONS

3+
Section F LIBRARY COLLECTIONS 

4
5
            Books/Serials/Volumes - Physical print format

6-
            F1 Number added__________________________

6+
            F1 Number added__________________________ 

7-
            F2 Number weeded__________________________

7+
            F2 Number weeded__________________________ 

8-
            F3 TOTAL__________________________

8+
            F3 TOTAL__________________________ 

9
            Current Print Serial Subscriptions (physical format, newspaper and periodicals)

10-
            F4 Number added__________________________

10+
            F4 Number added__________________________ 

11-
            F5 Number weeded__________________________

11+
            F5 Number weeded__________________________ 

12-
            F6 TOTAL__________________________

12+
            F6 TOTAL__________________________ 

13-
           

13+
            

14
            Audio materials (physical units containing music, spoken word, etc. on CD, cassette tape, etc.)

15-
            F7 Number of physical units added__________________________

15+
            F7 Number of physical units added__________________________ 

16-
            F8 Number of physical units weeded__________________________

16+
            F8 Number of physical units weeded__________________________ 

17-
            F9 TOTAL__________________________

17+
            F9 TOTAL__________________________ 

18-
           

18+
            

19
            Video materials (physical units containing movies, TV shows, etc. on film, DVD, etc.)

20-
            F10 Number of physical units added__________________________

20+
            F10 Number of physical units added__________________________ 

21-
            F11 Number of physical units weeded__________________________

21+
            F11 Number of physical units weeded__________________________ 

22-
            F12 TOTAL physical units held__________________________

22+
            F12 TOTAL physical units held__________________________ 

23
24
*/

25
26
27
/*

28
total language materials (aka books) in collection at end of fiscal

29
there isn't an "other" category for materials so everything else is grouped into physical print

30-
also adds in an estimate based on your non-cataloged circulation of those materials

30+
also adds in an estimate based on your non-cataloged circulation of those materials 

31
------------------------------------------------------------------------------------------------------------------

32
------------------------------------------------------------------------------------------------------------------

33
*/

34
SELECT SUM(a.copy_id) AS "Books Total - F3", a.org2_shortname

35
FROM (

36-
DROP TABLE IF EXISTS rogan.asset_copy;

36+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

37
        FROM asset.COPY COPY

38-
/* create a table and map per circulation */

38+
39
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

40-
CREATE TABLE rogan.asset_copy AS

40+
        JOIN asset.call_number acn ON acn.id = COPY.call_number

41-
SELECT id, circ_lib, LOCATION, circ_modifier, deleted, create_date, edit_date FROM asset.COPY;

41+
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

42
        WHERE COPY.deleted = FALSE AND date(COPY.create_date) <= '2015-06-30'

43-
ALTER TABLE rogan.asset_copy ADD COLUMN book BOOLEAN DEFAULT TRUE;

43+
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

44-
ALTER TABLE rogan.asset_copy ADD COLUMN serial BOOLEAN DEFAULT FALSE;

44+
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'

45-
ALTER TABLE rogan.asset_copy ADD COLUMN video BOOLEAN DEFAULT FALSE;

45+
46-
ALTER TABLE rogan.asset_copy ADD COLUMN audio BOOLEAN DEFAULT FALSE;

46+
47
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

48-
UPDATE rogan.asset_copy SET book = FALSE WHERE circ_modifier IN

48+
        FROM asset.COPY COPY

49-
        ('ELECTRONIC EQUIPMENT','EREADER','EQUIPMENT','INTERNET','THEME KIT','GAME');

49+
50
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

51-
UPDATE rogan.asset_copy SET serial = TRUE WHERE circ_modifier = 'MAGAZINE';

51+
        JOIN asset.call_number acn ON acn.id = COPY.call_number

52
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

53-
UPDATE rogan.asset_copy SET video = TRUE WHERE circ_modifier IN ('VIDEO','LEASE DVD');

53+
54
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

55-
UPDATE rogan.asset_copy SET audio = TRUE WHERE circ_modifier IN ('AUDIO','PLAYAWAY','AUDIO BOOK');

55+
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

56
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'

57-
ALTER TABLE rogan.asset_copy ADD COLUMN location_name TEXT;

57+
58
UNION ALL

59-
UPDATE rogan.asset_copy SET location_name = loc.NAME

59+
	SELECT coalesce(count(circ.id),0) AS copy_id, org2.shortname AS org2_shortname

60-
FROM (SELECT ac.id AS "ac_id", acl.NAME FROM rogan.asset_copy ac JOIN asset.copy_location acl ON acl.id = ac.LOCATION) loc

60+
        FROM action.non_cataloged_circulation circ

61-
WHERE loc.ac_id = id ;

61+
        left JOIN actor.org_unit org ON org.id = circ.circ_lib

62
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

63-
UPDATE rogan.asset_copy SET video = TRUE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')

63+
        where date(circ.circ_time) >= '2014-07-01' AND date(circ.circ_time) <= '2015-06-30'

64-
AND (location_name ILIKE '%video%' OR location_name ILIKE '%dvd%');

64+
65
) a

66-
UPDATE rogan.asset_copy SET audio = TRUE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')

66+
67-
AND (location_name ILIKE '%audio%' OR location_name ILIKE '%books on%' OR location_name ILIKE '%music%');

67+
68
;

69-
UPDATE rogan.asset_copy SET book = FALSE WHERE circ_modifier IN ('21 DAY LEASE','14 DAY LEASE','7 DAY LEASE','28 DAY','14 DAY','21 DAY','7 DAY','RENTAL')

69+
70-
AND (location_name ILIKE '%equipment%' OR location_name ILIKE '%adult 360%' OR location_name ILIKE '%adult ps2%');

70+
71
/*

72-
UPDATE rogan.asset_copy SET book = FALSE WHERE serial = TRUE;

72+
serials looking at position 8, requires it be a print material like those counted above 

73-
UPDATE rogan.asset_copy SET book = FALSE WHERE video = TRUE;

73+
74-
UPDATE rogan.asset_copy SET book = FALSE WHERE audio = TRUE;

74+
75
*/

76
SELECT SUM(a.copy_id) AS "Serials - F6", a.org2_shortname

77
FROM (

78
        SELECT coalesce(COUNT(distinct COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

79-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

79+
        FROM asset.COPY COPY

80-
        FROM rogan.asset_copy COPY

80+
81
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

82
        JOIN asset.call_number acn ON acn.id = COPY.call_number

83
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

84-
        AND COPY.book = TRUE

84+
85
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'

86
		AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

87-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

87+
88-
        FROM rogan.asset_copy COPY

88+
		GROUP BY 2

89
UNION ALL

90
        SELECT coalesce(COUNT(distinct COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

91
        FROM asset.COPY COPY

92-
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'

92+
93-
        AND COPY.book = TRUE

93+
94
        JOIN asset.call_number acn ON acn.id = COPY.call_number

95
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

96-
        SELECT COALESCE(COUNT(circ.id),0) AS copy_id, org2.shortname AS org2_shortname

96+
97-
        FROM ACTION.non_cataloged_circulation circ

97+
98-
        LEFT JOIN actor.org_unit org ON org.id = circ.circ_lib

98+
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'

99
		AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

100-
        WHERE date(circ.circ_time) between '2014-07-01' AND '2015-06-30'

100+
101
) a

102
GROUP BY 2

103
ORDER BY 2

104
;

105
106
107
/*

108
total j = music and i = audio books  in collection at end of fiscal

109
------------------------------------------------------------------------------------------------------------------

110
------------------------------------------------------------------------------------------------------------------

111
*/

112
SELECT SUM(a.copy_id) AS "Audio - F9", a.org2_shortname

113
FROM (

114-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

114+
        SELECT coalesce(COUNT(distinct COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

115-
        FROM rogan.asset_copy COPY

115+
        FROM asset.COPY COPY

116
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

117
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

118
        JOIN asset.call_number acn ON acn.id = COPY.call_number

119-
        AND COPY.serial = TRUE

119+
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

120
        WHERE COPY.deleted = FALSE AND date(COPY.create_date) <= '2015-06-30'

121
        AND meta.tag = 'LDR' AND (SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'j' OR SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'i')

122-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

122+
123-
        FROM rogan.asset_copy COPY

123+
124
        SELECT coalesce(COUNT(distinct COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

125
        FROM asset.COPY COPY

126
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

127-
        AND COPY.serial = TRUE

127+
128-
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'

128+
        JOIN asset.call_number acn ON acn.id = COPY.call_number

129
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

130
        WHERE COPY.deleted = TRUE AND date(COPY.create_date) <= '2015-06-30'

131
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

132
        AND meta.tag = 'LDR' AND (SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'j' OR SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'i')

133
        GROUP BY 2

134
) a

135
GROUP BY 2

136
ORDER BY 2

137
;

138
 

139
140
/*

141
total videos in collection at end of fiscal

142-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

142+
143-
        FROM rogan.asset_copy COPY

143+
144
answers F12

145
------------------------------------------------------------------------------------------------------------------

146
------------------------------------------------------------------------------------------------------------------

147-
        AND COPY.audio = TRUE

147+
148
SELECT SUM(a.copy_id) AS "Total Videos - F12", a.org2_shortname

149
FROM (

150-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

150+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

151-
        FROM rogan.asset_copy COPY

151+
        FROM asset.COPY COPY

152
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

153
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

154
        JOIN asset.call_number acn ON acn.id = COPY.call_number

155
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

156-
        AND COPY.audio = TRUE

156+
157
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'

158
        GROUP BY 2

159
UNION ALL

160
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

161
        FROM asset.COPY COPY

162
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

163
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

164
        JOIN asset.call_number acn ON acn.id = COPY.call_number

165
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = acn.record

166
        WHERE COPY.deleted = TRUE AND date(COPY.create_date) <= '2015-06-30'

167
        AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

168
        AND meta.tag = 'LDR' AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'

169
        GROUP BY 2

170
) a

171
GROUP BY 2

172
ORDER BY 2

173
;

174-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

174+
175-
        FROM rogan.asset_copy COPY

175+
176
/*

177
materials added within the fiscal year

178
-------------------------------------- looks for a = language material, j = music, g = projected medium

179-
        AND COPY.video = TRUE

179+
180
------------------------------------------------------------------------------------------------------------------

181
------------------------------------------------------------------------------------------------------------------

182-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS copy_id, org2.shortname AS org2_shortname

182+
183-
        FROM rogan.asset_copy COPY

183+
184
SELECT v.shortname AS "Library",

185
coalesce(SUM(t.books),0) AS "Books Added - F1", coalesce(SUM(e.serials),0) AS "Serials Added - F4", 

186
coalesce(SUM(s.sound),0) AS "Audios Added - F7", coalesce(SUM(v.videos),0) AS "Videos Added - F10"

187-
        AND COPY.video = TRUE

187+
188-
        and date(copy.edit_date) between '2014-07-01' and '2015-06-30'

188+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS videos, org2.shortname AS shortname

189
        FROM asset.COPY COPY

190
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

191
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

192
        JOIN asset.call_number call ON call.id = COPY.call_number

193
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

194
        WHERE date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'

195
        AND meta.tag = 'LDR'

196
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'

197
        GROUP BY 2

198
) v

199
left JOIN (

200
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname

201
        FROM asset.COPY COPY

202
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

203
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

204
        JOIN asset.call_number call ON call.id = COPY.call_number

205-
COALESCE(SUM(t.books),0) AS "Books Added - F1", COALESCE(SUM(e.serials),0) AS "Serials Added - F4",

205+
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

206-
COALESCE(SUM(s.sound),0) AS "Audios Added - F7", COALESCE(SUM(v.videos),0) AS "Videos Added - F10"

206+
207
        AND meta.tag = 'LDR'

208-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS videos, org2.shortname AS shortname

208+
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

209-
        FROM rogan.asset_copy COPY

209+
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'

210
        GROUP BY 2

211
) t ON t.shortname = v.shortname

212
left JOIN (

213-
        AND COPY.video = TRUE

213+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS sound, org2.shortname AS shortname

214
        FROM asset.COPY COPY

215
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

216-
LEFT JOIN (

216+
217-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname

217+
        JOIN asset.call_number call ON call.id = COPY.call_number

218-
        FROM rogan.asset_copy COPY

218+
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

219
        WHERE date(COPY.create_date) >= '2014-07-01' AND date(COPY.create_date) <= '2015-06-30'

220
        AND meta.tag = 'LDR'

221
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('i','j')

222-
                AND COPY.book = TRUE

222+
223
) s ON s.shortname = v.shortname

224
left JOIN (

225-
LEFT JOIN (

225+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS serials, org2.shortname AS shortname

226-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS sound, org2.shortname AS shortname

226+
        FROM asset.COPY COPY

227-
        FROM rogan.asset_copy COPY

227+
228
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

229
        JOIN asset.call_number call ON call.id = COPY.call_number

230
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

231-
                AND COPY.audio = TRUE

231+
232
        AND meta.tag = 'LDR'

233
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

234-
LEFT JOIN (

234+
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'

235-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS serials, org2.shortname AS shortname

235+
236-
        FROM rogan.asset_copy COPY

236+
237
GROUP BY 1

238
ORDER BY 1

239
;

240-
                AND COPY.serial = TRUE

240+
241
 

242
/*

243
materials weeded by end of fiscal - deleted or not

244
-------------------------------------- looks for a = language material, j = music, g = projected medium

245
-------------------------------------- i = audio books are bundeled with j music as per SC requirements

246
------------------------------------------------------------------------------------------------------------------

247
------------------------------------------------------------------------------------------------------------------

248
*/

249
 

250
SELECT v.shortname AS "Library",

251
coalesce(SUM(t.books),0) AS "Books Weeded - F2", coalesce(SUM(e.serials),0) AS "Serials Weeded - F5 ",

252
coalesce(SUM(s.sound),0) AS "Audio Weeded - F8", coalesce(SUM(v.videos),0) AS "Videos Weeded - F11"

253
FROM (

254
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS videos, org2.shortname AS shortname

255
        FROM asset.COPY COPY

256
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

257-
COALESCE(SUM(t.books),0) AS "Books Weeded - F2", COALESCE(SUM(e.serials),0) AS "Serials Weeded - F5 ",

257+
258-
COALESCE(SUM(s.sound),0) AS "Audio Weeded - F8", COALESCE(SUM(v.videos),0) AS "Videos Weeded - F11"

258+
        JOIN asset.call_number call ON call.id = COPY.call_number

259
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

260-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS videos, org2.shortname AS shortname

260+
261-
        FROM rogan.asset_copy COPY

261+
        AND meta.tag = 'LDR'

262
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) = 'g'

263
        GROUP BY 2

264
) v

265-
                AND COPY.video = TRUE

265+
left JOIN (

266
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname

267
        FROM asset.COPY COPY

268-
LEFT JOIN (

268+
269-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS books, org2.shortname AS shortname

269+
270-
        FROM rogan.asset_copy COPY

270+
        JOIN asset.call_number call ON call.id = COPY.call_number

271
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

272
        WHERE COPY.deleted = TRUE AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

273
        AND meta.tag = 'LDR'

274-
                AND COPY.book = TRUE

274+
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

275
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) != 's'

276
        GROUP BY 2

277-
LEFT JOIN (

277+
278-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS sound, org2.shortname AS shortname

278+
left JOIN (

279-
        FROM rogan.asset_copy COPY

279+
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS sound, org2.shortname AS shortname

280
        FROM asset.COPY COPY

281
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

282
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

283-
                AND COPY.audio = TRUE

283+
        JOIN asset.call_number call ON call.id = COPY.call_number

284
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

285
        WHERE COPY.deleted = TRUE AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

286-
LEFT JOIN (

286+
        AND meta.tag = 'LDR'

287-
        SELECT COALESCE(COUNT(DISTINCT COPY.id),0) AS serials, org2.shortname AS shortname

287+
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('i','j')

288-
        FROM rogan.asset_copy COPY

288+
289
) s ON s.shortname = v.shortname

290
left JOIN (

291
        SELECT coalesce(COUNT(DISTINCT COPY.id),0) AS serials, org2.shortname AS shortname

292-
                AND COPY.serial = TRUE

292+
        FROM asset.COPY COPY

293
        JOIN actor.org_unit org ON org.id = COPY.circ_lib

294
        JOIN actor.org_unit org2 ON org2.id = org.parent_ou

295
        JOIN asset.call_number call ON call.id = COPY.call_number

296
        LEFT OUTER JOIN metabib.real_full_rec meta ON meta.record = call.record

297
        WHERE COPY.deleted = TRUE AND date(COPY.edit_date) >= '2014-07-01' AND date(COPY.edit_date) <= '2015-06-30'

298
        AND meta.tag = 'LDR'

299
        AND SUBSTRING(meta.VALUE FROM 7 FOR 1) IN ('a','c','d','e','f','k','m','o','p','r','t')

300
        AND SUBSTRING(meta.VALUE FROM 8 FOR 1) = 's'

301
        GROUP BY 2

302
) e ON e.shortname = v.shortname

303
GROUP BY 1

304
ORDER BY 1

305
;
        

    


            


                                


        

            



                
    

        Public Pastes
    

    
            

    

                    

        

    





    


    
    
    
    
    
    
    
    




    


    



                

            We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.             OK, I Understand
        

    
                

            

                
                    
                
            

            

                Not a member of Pastebin yet?

                Sign Up, it unlocks many cool features!