From: Frank Bax <fbax@sympatico.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 09:58:34 -0400
Message-ID: <47DA848A.9000700@sympatico.ca> (raw)
In-Reply-To: <47DA8108.1020104@sympatico.ca>
References: <200803132025.27821.aarni@kymi.com>
<200803132209.33914.aarni@kymi.com>
<47D98D11.4080406@pinpointresearch.com>
<200803141504.54211.aarni@kymi.com>
<47DA8108.1020104@sympatico.ca>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: fbax@sympatico.ca
Subject: Re: Counting days ...
In-Reply-To: <47DA848A.9000700@sympatico.ca>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox