Funny thing.
In mine, it works...
On mine, If I subtract one date from the other I get the number of days in between...which is another integer, I guess. and '%' is the modular operator. Doesnt it work like this on yours?
Which version are you using?
try
SELECT (date - '2007-01-01') % 2 FROM tableto see if you get zeroes and ones .
Cheers,
Oliveiros
2007/5/9, Loredana Curugiu <loredana.curugiu@gmail.com >:
You need a query that returns the number of receivers on each theme, in two days intervals, is this correct?
yes, this is correct. Actually the interval in days is variable ( it would specified using PreparedStatemtent ).
Sorry I didn't say from the begining.
Please try something like this. I am not sure if it works, because I don't have a table like yours.
I am assuming your table is called table
SELECT a."date",a."theme",a.receiver,COUNT(*)
FROM table a
INNER JOIN table b
ON a."theme" = b."theme" AND a.receiver = b.receiver
WHERE (((b."date" - a."date") = 1) OR (b."date" - a."date") = 0)
GROUP BY a."date",a."theme",a.receiver
HAVING (a."date" - '2007-01-01' ) % 2 = 0
ORDER BY a."date"
ERROR: operator does not exist: interval % integer
HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts.
