Message-ID: <47D97966.3030203@pinpointresearch.com>
Date: Thu, 13 Mar 2008 11:58:46 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: aarni@kymi.com
CC: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
References: <200803132025.27821.aarni@kymi.com>
In-Reply-To: <200803132025.27821.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Aarni Ruuhim=E4ki wrote:
> Hi all,
>
> A bit stuck here with something I know I can do with output / loops /=20
> filtering in the (web)application but want to do in SQL or within Postg=
reSQL.
>
> Simply said, count days of accommodation for a given time period.
>
> E.g.
>
> res_id 1, start_day 2008-01-25, end_day 2008-02-15, number of persons 6
> res_id 2, start_day 2008-02-10, end_day 2008-02-15, number of persons 4
>
> for the period from 2008-02-01 to 2008-02-29 these two rows would give =
a total=20
> of
>
> 15 days x 6 persons + 4 days x 5 persons =3D 110 days
>
> SELECT SUM(
> CASE
> WHEN res_start_day >=3D '2008-01-01' THEN
> (res_end_day - res_start_day)
> ELSE (res_end_day - (DATE '2008-01-01' - INTEGER '1'))
> END
> * group_size) AS days_in_period
> FROM product_res pr
> WHERE res_end_day >=3D '2008-01-01' AND res_end_day <=3D '2008-12-31';
>  =20
This appears fraught with off-by-one and other errors.

For res_id 1 limited to the month of February you do indeed have 6=20
persons and 15 days =3D 90 person-days as you are including day 1 and day=
 15.

If you use the same inclusive counting of days for res_id 2, you have 4=20
persons (don't know where 5 came from) and 6 days for 24 person-days.

I'm making an assumption that you have reservations with arbitrary start=20
and end dates (assumed to be inclusive of both start and end) along with=20
group size and you want to see the person-days utilized within a=20
specified period.

First, to simply establish upper and lower bounds,=20
date_larger/date_smaller seems a lot easier - ie. for February inclusive=20
dates:

select
sum (
((date_smaller(res_end_day, '2008-02-29'::date)
  - date_larger(res_start_day, '2008-02-01'::date))::int + 1) * group_siz=
e
) 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 nationa=
lities,=20
> like:
>
> FI 12345
> RU 9876
> DE 4321
> ...
>  =20
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_siz=
e
) as person_days
group by country_id;

Add where-clauses to either for efficiency.

Cheers,
Steve