Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 2F0AD2E0197
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Mar 2008 17:10:00 -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 68543-01-7 for <pgsql-sql-postgresql.org@postgresql.org>;
	Thu, 13 Mar 2008 17:09:52 -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 82C2F2E013D
	for <pgsql-sql@postgresql.org>; Thu, 13 Mar 2008 17:09:24 -0300 (ADT)
Received: from kymifedora ([213.130.241.66])
	by smtp20.kymp.net (8.13.3/8.13.3) with ESMTP id m2DK9H8S000353;
	Thu, 13 Mar 2008 22:09:18 +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: Thu, 13 Mar 2008 22:09:33 +0200
User-Agent: KMail/1.6.2
References: <200803132025.27821.aarni@kymi.com>
	<47D97966.3030203@pinpointresearch.com>
In-Reply-To: <47D97966.3030203@pinpointresearch.com>
Cc: Steve Crawford <scrawford@pinpointresearch.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200803132209.33914.aarni@kymi.com>
X-Virus-Scanned: by amavisd-new
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200803/76
X-Sequence-Number: 30404

This was superfast, thank you !

On Thursday 13 March 2008 20:58, Steve Crawford wrote:
> Aarni Ruuhim=E4ki wrote:

> > res_id 2, start_day 2008-02-10, end_day 2008-02-15, number of persons 4
> >
>
> If you use the same inclusive counting of days for res_id 2, you have 4
> persons (don't know where 5 came from) and 6 days for 24 person-days.
>

Sorry, my bad, I meant 5 days x 4 persons =3D 20 person-days. From 10 to 15=
 it=20
is 5 'days' ? Arrival 10th and departure 15th makes 5 'nights' or 'days',=20
whatever one might call it, statistical accommodation units.

Likewise, res_id x, start_day some time before Feb 08, end_day 2008-02-16 f=
or=20
a statistical period Feb 08 makes 16 units ?

>
> First, to simply establish upper and lower bounds,
> date_larger/date_smaller seems a lot easier - ie. for February inclusive
> dates:
>
> select
> sum (
> ((date_smaller(res_end_day, '2008-02-29'::date)
>   - date_larger(res_start_day, '2008-02-01'::date))::int + 1) * group_size
> ) as person_days;
>
> > Country_id is also stored in the product_res table.
> >
> > I would like to, or need to, get the total split into different
> > nationalities, like:
> >
> > FI 12345
> > RU 9876
> > DE 4321
> > ...
>
> OK.
>
> select
> country_id,
> sum (
> ((date_smaller(res_end_day, '2008-02-29'::date)
>   - date_larger(res_start_day, '2008-02-01'::date))::int + 1) * group_size
> ) as person_days
> group by country_id;

Ok, thanks, I'll dig into this.

>
> Add where-clauses to either for efficiency.

AND region_id =3D x<if>IsDefined(company_id), AND company_id =3D x</if>=20
<if>IsDefined(product_id), AND product_id =3D x</if>
>
> Cheers,
> Steve

 days_in_period |      country
=2D---------------+--------------------
           5519 | Unknown
             16 | Germany
             18 | Estonia
           3061 | Russian Federation
           1491 | Suomi
             20 | Ukraine
(6 rows)

Getting there.

Cheers to all you amazingly helpful folks out there,

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