Re: Counting days ... - Mailing list pgsql-sql

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.