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: Counting days ...
Date: Thu, 13 Mar 2008 20:25:27 +0200
User-Agent: KMail/1.6.2
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200803132025.27821.aarni@kymi.com>

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 PostgreSQ=
L.

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 to=
tal=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
=46ROM product_res pr
WHERE res_end_day >=3D '2008-01-01' AND res_end_day <=3D '2008-12-31';

 days_in_period
=2D---------------
           68383
(1 row)

Ok, but a reservation can be of any nationality / country:

SELECT count(country_id) FROM countries;
 count
=2D------
   243
(1 row)

Country_id is also stored in the product_res table.

I would like to, or need to, get the total split into different nationaliti=
es,=20
like:

=46I 12345
RU 9876
DE 4321
=2E..

Anyone ?

With very best regards,

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