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 19:25:26 +0200
User-Agent: KMail/1.6.2
References: <200803132025.27821.aarni@kymi.com>
	<200803141713.40589.aarni@kymi.com> <47DAA330.8070409@sympatico.ca>
In-Reply-To: <47DAA330.8070409@sympatico.ca>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200803141925.26039.aarni@kymi.com>

On Friday 14 March 2008 18:09, Frank Bax wrote:
> >
> > Is zero days/nights, ignored. Not even possible to insert in the
> > application. end_day must be greater than start_day.
>
> You should still consider rows that are "out of range" or "zero nights"
> in your test cases to make sure your report processes them correctly.

=46or the WHERE clause for Jan 08 (will be $date1 and $date2), getting ther=
e.=20

1. start_day before period_start, end_day =3D period_start #1
2. start_day before period_start, end_day in period #1
3. start_day before period_start, end_day =3D period_end #1
4. start_day =3D period_start, end_day in period #2
5. start_day in period, end_day in period #3
6. start_day =3D period_start, end_day =3D period_end #2
7. start_day in period, end_day =3D period_end #3
8. start_day in period, end_day after period_end #3
9. start_day =3D period_start, end_day after period_end #2
10. start_day before period_start, end_day after period_end #1


SELECT res_id, to_char(res_start_day, 'DD.MM.YYYY'), to_char(res_end_day,=20
'DD.MM.YYYY')
=46ROM product_res
WHERE=20
group_id =3D 1 AND res_start_day < '2008-01-01' AND res_end_day >=3D '2008-=
01-01'=20
# covers 1,2,3,10
OR
group_id =3D 1 AND res_start_day =3D '2008-01-01' AND res_end_day >=3D '200=
8-01-01'=20
# covers 4,6,9
OR
group_id =3D 1 AND res_start_day >=3D '2008-01-01' AND res_start_day <=20
'2008-01-31' AND res_end_day >=3D '2008-01-01'; # covers 5,7,8

(499 rows) not yet summing up or grouping by.

But is this getting too heavy ? I have three more (optional) parameters to=
=20
pass into the query, which narrow down the result. All values are stored al=
so=20
in the product_res table.

1. Area/region ID from dropdown, populated by areas that have products
2. Company ID from dropdown, dynamically populated according to the optiona=
l=20
area selection with companies that have products in the selected area
3. Product ID from dropdown, dynamically populated by the optional company=
=20
selection with the selected company's products in the selected area

So the WHERE clause would go like:

group_id =3D 1 AND res_start_day < '$date1' AND res_end_day >=3D '$date1' [=
AND=20
region_id =3D $region_id] [AND company_id =3D $company_id] [AND product_id =
=3D=20
$product_id]
OR=20
group_id =3D 1 AND res_start_day =3D '$date1' AND res_end_day >=3D '$date1'=
 [AND=20
region_id =3D $region_id] [AND company_id =3D $company_id] [AND product_id =
=3D=20
$product_id]
OR
group_id =3D 1 AND res_start_day >=3D '$date1' AND res_start_day < '$date2'=
 AND=20
res_end_day >=3D '$date1' [AND region_id =3D $region_id] [AND company_id =
=3D=20
$company_id] [AND product_id =3D $product_id]

Cheerio,

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