Loredana Curugiu wrote:
You don't actually say what's wrong. What are you expecting as output?
I should obtain the following result:
[snip]
Well, I've attached a test script using your example data and a copy of
my results. Nothing leaping out as wrong here. It's entirely possible
I've not had enough coffee today though and I'm missing something
staring me in the face...
--
Richard Huxton
Archonet Ltd
sum | theme | receiver | date |
dates
-----+-------+--------------+------------------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
3 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
18 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
4 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
6 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
10 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
(8 rows)
count | theme | receiver | date |
dates
-------+-------+--------------+------------------------+--------------------------------------------------------------------------------
3 | CRIS | +40741775622 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-02 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-03 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | CRIS | +40741775622 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4 | LIA | +40741775621 | 2007-06-01 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
2 | LIA | +40741775621 | 2007-06-02 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
2 | LIA | +40741775621 | 2007-06-03 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1 | LIA | +40741775621 | 2007-06-04 00:00:00+00 |
{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
(40 rows)
/*
CREATE TABLE loredana_test (
count int4,
theme text,
receiver text,
"date" timestamptz,
dates date[]
);
COPY loredana_test FROM stdin WITH DELIMITER '|';
2|LIA|+40741775621|2007-06-02 00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
3|CRIS|+40741775622|2007-06-01 00:00:00+00|{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07}
1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
2|LIA|+40741775621|2007-06-03 00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
1|CRIS|+40741775622|2007-06-03 00:00:00+00|{2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1|CRIS|+40741775622|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09,2007-06-10}
4|LIA|+40741775621|2007-06-01 00:00:00+00|{2007-06-01,2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06}
1|LIA|+40741775621|2007-06-04 00:00:00+00|{2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08,2007-06-09}
1|CRIS|+40741775622|2007-06-02 00:00:00+00|{2007-06-02,2007-06-03,2007-06-04,2007-06-05,2007-06-06,2007-06-07,2007-06-08}
\.
*/
-- Uncomment one of these to control whether any rows match
SET timezone = 'GMT';
-- SET timezone = 'GB';
-- Original query, but with the "date" column displayed too
SELECT
SUM(A.count),
A.theme,
A.receiver,
A.date,
A.dates
FROM
loredana_test A
INNER JOIN
loredana_test B
ON
A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = ANY(B.dates)
GROUP BY
A.theme,A.receiver,A.date,A.dates;
-- Query to test matches
SELECT
A.count,
A.theme,
A.receiver,
A.date,
B.dates
FROM
loredana_test A
INNER JOIN
loredana_test B
ON
A.theme=B.theme
AND A.receiver=B.receiver
AND A.date = ANY(B.dates)
ORDER BY
A.theme, A.receiver, A.date
;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
