Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id F2ACB2E2F47
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 15 Mar 2008 14:06:30 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 49346-04 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 15 Mar 2008 14:06:26 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.5
Received: from smtp20.kymp.net (smtp20.kymp.net [80.248.96.137])
	by postgresql.org (Postfix) with ESMTP id 0ABAA2E2F74
	for <pgsql-sql@postgresql.org>; Sat, 15 Mar 2008 14:06:27 -0300 (ADT)
Received: from kymifedora ([213.130.241.66])
	by smtp20.kymp.net (8.13.3/8.13.3) with ESMTP id m2FH6Pea022566;
	Sat, 15 Mar 2008 19:06:26 +0200
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: Sat, 15 Mar 2008 19:06:43 +0200
User-Agent: KMail/1.6.2
References: <200803132025.27821.aarni@kymi.com>
	<200803141925.26039.aarni@kymi.com> <47DBF3BD.9080306@sympatico.ca>
In-Reply-To: <47DBF3BD.9080306@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: <200803151906.43271.aarni@kymi.com>
X-Virus-Scanned: by amavisd-new
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/89
X-Sequence-Number: 30417

On Saturday 15 March 2008 18:05, Frank Bax wrote:

> This is smaller; and should be equivalent:
>
> group_id =3D 1 AND
> ( res_start_day <=3D '$date1' AND res_end_day >=3D '$date1'
> OR
> res_start_day >=3D '$date1' AND res_start_day < '$date2' )
> [AND region_id =3D $region_id]
> [AND company_id =3D $company_id]
> [AND product_id =3D  $product_id]

You're quite right.

Here's the whole thing in cfml as it is now. Explain analyze for year 2007=
=20
gives runtime 49.675 ms, which is not bad I think. The total page rendering=
=20
time to browser is 950 ms.

SELECT =20
SUM(=20
CASE=20
WHEN res_start_day < '#date1#' AND res_end_day =3D '#date1#' THEN (res_end_=
day -=20
(DATE '#date1#' - INTEGER '1'))=20
WHEN res_start_day < '#date1#' AND res_end_day >=3D '#date1#' AND res_end_d=
ay <=3D=20
'#date2#' THEN (res_end_day - (DATE '#date1#' - INTEGER '1'))=20
WHEN res_start_day < '#date1#' AND res_end_day =3D '#date2#' THEN (res_end_=
day -=20
(DATE '#date1#' - INTEGER '1'))=20
WHEN res_start_day =3D '#date1#' AND res_end_day >=3D '#date1#' AND res_end=
_day <=3D=20
'#date2#' THEN (res_end_day - '#date1#')=20
WHEN res_start_day >=3D '#date1#' AND res_start_day <=3D '#date2#' AND res_=
end_day=20
>=3D '#date1#' AND res_end_day <=3D '#date2#' THEN (res_end_day - res_start=
_day)=20
WHEN res_start_day >=3D '#date1#' AND res_start_day <=3D '#date2#' AND res_=
end_day=20
> '#date2#' THEN ('#date2#' - res_start_day)=20
WHEN res_start_day =3D '#date1#' AND res_end_day > '#date2#' THEN ('#date2#=
' -=20
res_start_day)=20
WHEN res_start_day < '#date1#' AND res_end_day > '#date2#' THEN ('#date2#' =
=2D=20
(DATE '#date1#' - INTEGER '1'))=20
END=20
* group_size) AS person_days_in_period,=20
c.country_name AS country=20
=46ROM product_res pr=20
LEFT JOIN countries c ON pr.country_id =3D c.country_id=20
WHERE =20
group_id =3D 1 AND group_size > 0 AND res_start_day <=3D '#date1#' AND res_=
end_day=20
>=3D '#date1#' AND res_end_day > res_start_day=20
<cfif form.region GT 0>AND region_id =3D #form.region#</cfif>=20
<cfif form.company GT 0>AND company_id =3D #form.companyt#</cfif>=20
<cfif form.product GT 0>AND product_id =3D #form.product#</cfif>=20
AND res_cancelled IS NOT TRUE=20
OR=20
group_id =3D 1 AND group_size > 0 AND res_start_day >=3D '#date1#' AND=20
res_start_day < '#date2#' AND res_end_day >=3D '#date1#' AND res_end_day >=
=20
res_start_day=20
<cfif form.region GT 0>AND region_id =3D #form.region#</cfif>=20
<cfif form.company GT 0>AND company_id =3D #form.companyt#</cfif>=20
<cfif form.product GT 0>AND product_id =3D #form.product#</cfif>=20
AND res_cancelled IS NOT TRUE=20
group by pr.country_id, c.country_name;

Thank you guys again,

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