completing previous commit (hold loop calcuation upgrade scripts)
[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / 0003.schema.hold-loop-counting.sql
1 BEGIN;
2
3 INSERT INTO config.upgrade_log (version) VALUES ('0003.schema.hold-loop-counting.sql');
4
5 CREATE OR REPLACE VIEW action.unfulfilled_hold_loops AS
6     SELECT  u.hold,
7             c.circ_lib,
8             count(*)
9       FROM  action.unfulfilled_hold_list u
10             JOIN asset.copy c ON (c.id = u.current_copy)
11       GROUP BY 1,2;
12
13 CREATE OR REPLACE VIEW action.unfulfilled_hold_min_loop AS
14     SELECT  hold,
15             min(count)
16       FROM  action.unfulfilled_hold_loops
17       GROUP BY 1;
18
19 CREATE OR REPLACE VIEW action.unfulfilled_hold_innermost_loop AS
20     SELECT  DISTINCT l.*
21       FROM  action.unfulfilled_hold_loops l
22             JOIN action.unfulfilled_hold_min_loop m USING (hold)
23       WHERE l.count = m.min;
24
25
26 COMMIT;