Hey all....
I need to join two tables and retrieve a 75 X 15 column matrix for all the
records in table 1 across a 13 week range so the output should look like
this. I would like the missing records in Table 2 to populate the structure
with 0.00...
Week à
Type Code
Description
14
15
16
17
18
19
20
21
22
23
24
25
26
AC1
Activity 1
0.0
0.0
1.35
2.10
2.56
3.12
4.00
5.00
12.10
17.50
21.90
25.00
45.50
AC1
Activity 2
2.00
1.34
1.35
2.10
2.56
8.00
8.00
12.11
0.00
0.00
0.00
5.00
4.00
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
GROUP TOTALS
2.00
1.34
2.70
4.20
5.12
11.12
12.00
17.11
12.10
17.50
21.90
30.00
49.5
B1
Billing 1
B2
Billing 2
GROUP TOTALS
The two table definitions:
CREATE TABLE l_activity_type
(
id serial NOT NULL PRIMARY KEY,
activity_group int4 NOT NULL DEFAULT 0,
type_code varchar(5) NOT NULL DEFAULT ''::character varying,
type_desc varchar(50) NOT NULL DEFAULT ''::character varying,
display_order int4 NOT NULL DEFAULT 0,
calc_order int4 NOT NULL DEFAULT 0,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
active_flag bool DEFAULT true
) WITH OIDS;
l_activity_type has 75 rows
CREATE TABLE l_store_hours
(
id serial NOT NULL PRIMARY KEY,
l_store_id int4 NOT NULL DEFAULT 0,
l_activity_type int4 NOT NULL REFERENCES l_activity_type(id),
week_code int4 NOT NULL DEFAULT 0,
year_code int4 NOT NULL DEFAULT 0,
amount numeric(10,2) DEFAULT 0.00,
create_dt timestamp NOT NULL DEFAULT now(),
change_dt timestamp NOT NULL DEFAULT now(),
change_id int4 DEFAULT 0,
active_flag bool DEFAULT true
) WITH OIDS;
l_store_hours has 2,131,250 rows approximately...
So far -- I have written the following function which returns a type of
"storehours":
DROP TYPE storehours CASCADE;
CREATE TYPE storehours AS (
activity_group INTEGER,
type_code VARCHAR(50),
display_order INTEGER,
type_desc VARCHAR(50),
week_code INTEGER,
amount NUMERIC(10,2)
);
-- DROP FUNCTION f_storehours();
CREATE OR REPLACE FUNCTION f_storehours(integer, integer, integer, integer)
RETURNS SETOF storehours AS '
DECLARE STORE ALIAS FOR $1;
DECLARE START ALIAS FOR $2;
DECLARE STOP ALIAS FOR $3;
DECLARE YEAR ALIAS FOR $4;
DECLARE ROW RECORD;
DECLARE WEEK INTEGER;
BEGIN
FOR WEEK IN START .. STOP LOOP
FOR ROW IN SELECT * FROM (SELECT LT.activity_group, LT.type_code,
LT.display_order, LT.type_desc,
WEEK as week_code, 0.00 AS amount FROM l_activity_type LT
UNION ALL
SELECT LT.activity_group, LT.type_code, LT.display_order, LT.type_desc,
LH.week_code,
LH.amount FROM l_activity_type LT
JOIN l_store_hours LH ON LT.id = LH.l_activity_type AND LH.l_store_id =
STORE
AND LH.week_code = WEEK AND LH.year_code = YEAR) AS A LOOP
RETURN NEXT ROW;
END LOOP;
END LOOP;
RETURN;
END;
' LANGUAGE PLPGSQL;
SELECT * FROM f_storehours(365, 14, 26, 2006) ORDER BY activity_group,
display_order, type_code, week_code;
EXPLAIN ANALYZE yields the following:
Sort (cost=62.33..64.83 rows=1000 width=211) (actual
time=16726.014..16726.286 rows=1618 loops=1)
Sort Key: activity_group, display_order, week_code
Regards,
Greg Patnude - Manager, Dynamic Applications Group
Data Mosaics, Inc.
2406 South Dishman-Mica Road / Suite # 6
Spokane Valley, WA 99206-6429
VOICE: (866) 904-DMSF
FAX: (509) 928-4236
-> Function Scan on f_storehours (cost=0.00..12.50 rows=1000 width=211)
(actual time=16720.839..16722.757 rows=1618 loops=1)
Total runtime: 16726.841 ms
(4 rows)
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
