From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni@kymi.com>
Reply-To: aarni@kymi.com
Organization: KYMI.com
To: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 17:13:40 +0200
User-Agent: KMail/1.6.2
References: <200803132025.27821.aarni@kymi.com>
	<47DA8108.1020104@sympatico.ca> <47DA848A.9000700@sympatico.ca>
In-Reply-To: <47DA848A.9000700@sympatico.ca>
Cc: Frank Bax <fbax@sympatico.ca>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200803141713.40589.aarni@kymi.com>

Thanks Frank,

Top and between posting ...

On Friday 14 March 2008 15:58, Frank Bax wrote:
> Frank Bax wrote:
> > Aarni Ruuhim=E4ki 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 =3D period_start
> >> 2. start_day before period_start, end_day in period
> >> 3. start_day before period_start, end_day =3D period_end
> >> 4. start_day =3D period_start, end_day in period
> >> 5. start_day in period, end_day in period
> >> 6. start_day =3D period_start, end_day =3D period_end
> >> 7. start_day in period, end_day =3D period_end
> >> 8. start_day in period, end_day after period_end
> >> 9. start_day =3D period_start, end_day =3D period_end
> >> 10 start_day before period_start, end_day after period_end
> >
> > #6 and #9 are the same.  You missed these:

Whoops, 9 should be c.

> >
> > a    start_day before period_start, end_day before period_start

This I don't have to care about as it is not in the period we are looking a=
t.

> > b    start_day =3D period_start, end_day =3D period_start

Is zero days/nights, ignored. Not even possible to insert in the applicatio=
n.=20
end_day must be greater than start_day.

> > c    start_day =3D period_start, end_day after period_end

Yes. Number 9 now.

> > d    start_day =3D period_end, end_day =3D period_end

Is zero days, ignored. like b.

> > e    start_day =3D period_end, end_day after period_end

Is outside the period. Day changes / the night starts at midnight, so this=
=20
would go in the 'next' period. Like number 1 comes in this period as one da=
y.

> > f    start_day after period_end, end_day after period_end

This is also outside the period we are looking at.

> >
> > 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 =3D 1 AND (res_start_day >=3D '2007-01-01' AND res_end_d=
ay <=3D
> > '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.

Yes, the application does not allow this.
SELECT res_id FROM product_res WHERE res_start_day > res_end_day;
 res_id
=2D-------
(0 rows)

> >
> > 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 =3D 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.

I think I need more ORs in the WHERE clause to find all res_ids I want to=20
count according to the 10 rules. Ie. if one or more days of a reservation i=
s=20
'inside' the given period.

Testing ...

Best regards,

=2D-=20
Aarni Ruuhim=E4ki
=2D--
Burglars usually come in through your windows.
=2D--