From: Aarni Ruuhimäki <aarni@kymi.com>
To: pgsql-sql@postgresql.org
Subject: Counting days ...
Date: Thu, 13 Mar 2008 20:25:27 +0200
Message-ID: <200803132025.27821.aarni@kymi.com> (raw)
Hi all,
A bit stuck here with something I know I can do with output / loops /
filtering in the (web)application but want to do in SQL or within PostgreSQL.
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
of
15 days x 6 persons + 4 days x 5 persons = 110 days
SELECT SUM(
CASE
WHEN res_start_day >= '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 >= '2008-01-01' AND res_end_day <= '2008-12-31';
days_in_period
----------------
68383
(1 row)
Ok, but a reservation can be of any nationality / country:
SELECT count(country_id) FROM countries;
count
-------
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 nationalities,
like:
FI 12345
RU 9876
DE 4321
...
Anyone ?
With very best regards,
--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aarni@kymi.com
Subject: Re: Counting days ...
In-Reply-To: <200803132025.27821.aarni@kymi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox