I forgot the attachement :)
On 6/6/07, Loredana Curugiu <[EMAIL PROTECTED]> wrote:
I found my problem. I attaches an .sql file with my tables, data
and my new query. If you are interested you can take a look.
Thanks to all.
Regards,
Loredana
CREATE TABLE reminder_services (
uid SERIAL,
theme_uid INT NOT NULL,
activity_MT_amount INT NOT NULL,
activity_min_days INT NOT NULL,
activity_max_months INT NOT NULL,
CONSTRAINT pk_reminder_services PRIMARY KEY(uid),
CONSTRAINT un_reminder_services_theme UNIQUE(theme_uid) );
CREATE TABLE themes (
uid SERIAL PRIMARY KEY,
theme VARCHAR(25) UNIQUE NOT NULL );
CREATE TABLE sent_messages (
uid SERIAL PRIMARY KEY,
receiver VARCHAR(25),
theme VARCHAR(25),
date TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP );
COPY reminder_services FROM stdin WITH DELIMITER '|';
5 | 6 | 3 | 6 | 1
6 | 7 | 4 | 5 | 1
\.
COPY themes FROM stdin WITH DELIMITER '|';
6 | CRISTI
7 | LIA
\.
COPY sent_messages FROM stdin WITH DELIMITER '|';
62 | +40741775622 | CRISTI | 2007-06-04 07:44:45.406271+00
70 | +40741775622 | CRISTI | 2007-06-04 07:45:01.788533+00
71 | +40741775622 | CRISTI | 2007-06-04 07:45:03.764506+00
80 | +40741775621 | LIA | 2007-06-04 07:45:26.309215+00
81 | +40741775621 | LIA | 2007-06-04 07:45:28.314075+00
63 | +40741775622 | CRISTI | 2007-06-03 07:44:00+00
64 | +40741775622 | CRISTI | 2007-06-02 07:44:00+00
65 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
66 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
67 | +40741775622 | CRISTI | 2007-06-01 07:44:00+00
68 | +40741775622 | CRISTI | 2007-04-01 07:44:00+00
69 | +40741775622 | CRISTI | 2007-05-01 07:44:00+00
72 | +40741775621 | LIA | 2007-06-03 07:44:00+00
73 | +40741775621 | LIA | 2007-06-03 07:44:00+00
74 | +40741775621 | LIA | 2007-06-02 07:44:00+00
75 | +40741775621 | LIA | 2007-06-02 07:44:00+00
76 | +40741775621 | LIA | 2007-06-01 07:44:00+00
77 | +40741775621 | LIA | 2007-06-01 07:44:00+00
78 | +40741775621 | LIA | 2007-06-01 07:44:00+00
79 | +40741775621 | LIA | 2007-06-01 07:44:00+00
\.
SELECT SUM(B.count),
A.theme,
A.receiver,
A.dates
FROM ( SELECT DISTINCT COUNT(*),
sent_messages.theme,
receiver,
ARRAY(SELECT date::date + s.a FROM generate_series(0,activity_min_days) AS s(a)) AS dates
FROM reminder_services, themes,sent_messages
WHERE themes.uid=reminder_services.theme_uid
AND TRIM(sent_messages.theme)=TRIM(themes.theme)
AND date_trunc('day',sent_messages.date) > (now() - reminder_services.activity_max_months * INTERVAL' 1 month')
GROUP BY sent_messages.theme, receiver, date, activity_min_days ) A
INNER JOIN ( SELECT COUNT(*),
sent_messages.theme,
receiver,
date_trunc('day',sent_messages.date) AS date
FROM reminder_services, themes,sent_messages
WHERE themes.uid=reminder_services.theme_uid
AND TRIM(sent_messages.theme)=TRIM(themes.theme)
AND date_trunc('day',sent_messages.date) > (now() - reminder_services.activity_max_months * INTERVAL' 1 month')
GROUP BY sent_messages.theme, receiver, date, activity_min_days ) B
ON A.theme=B.theme
AND A.receiver=B.receiver
AND B.date=ANY (A.dates)
GROUP BY A.theme, A.receiver, A.dates;
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
