Frank Bax wrote:
Aarni Ruuhimäki wrote:
Anyway, I have to rethink and elaborate the query. I know that it will
usually be on a monthly or yearly basis, but a reservation can
actually be any of the following in relation to the given (arbitrary)
period:
1. start_day before period_start, end_day = period_start
2. start_day before period_start, end_day in period
3. start_day before period_start, end_day = period_end
4. start_day = period_start, end_day in period
5. start_day in period, end_day in period
6. start_day = period_start, end_day = period_end
7. start_day in period, end_day = period_end
8. start_day in period, end_day after period_end
9. start_day = period_start, end_day = period_end
10 start_day before period_start, end_day after period_end
#6 and #9 are the same. You missed these:
a start_day before period_start, end_day before period_start
b start_day = period_start, end_day = period_start
c start_day = period_start, end_day after period_end
d start_day = period_end, end_day = period_end
e start_day = period_end, end_day after period_end
f start_day after period_end, end_day after period_end
Granted, a & f should not match where clause; but then groups 10,c,e
don't meet your where clause either. Your where clause should probably be:
WHERE group_id = 1 AND (res_start_day >= '2007-01-01' AND res_end_day <=
'2008-12-31')
Are you sure that your database does not have any rows where start_day
is after end_day? These rows could certainly skew results.
I would suggest that you identify a few rows that meet each of these
conditions. Change the where clause to select rows in one group at a
time. You might consider using a unique row identifier in where clause
during these tests to make sure you are processing the rows you think
you are. When all test cases work properly; then run your generalized
query again.
Change 10,c,e to 8,10,c,e - Group 8 also does not meet your initial
WHERE clause. My suggestion for WHERE clause also does not work. This
might work better (although it still could be wrong):
WHERE group_id = 1 AND (res_start_day BETWEEN '2007-01-01' AND
'2008-12-31' OR res_end_day BETWEEN '2007-01-01' AND '2008-12-31')
In case I still have it wrong, try each test group separately and you'll
soon find out if the WHERE clause is correct or not.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
