Skip site navigation (1) Skip section navigation (2)

Counting days ...

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Counting days ...
Date: 2008-03-13 18:25:27
Message-ID: 200803132025.27821.aarni@kymi.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.
---

Responses

pgsql-sql by date

Next:From: Steve CrawfordDate: 2008-03-13 18:58:46
Subject: Re: Counting days ...
Previous:From: JulienDate: 2008-03-13 16:50:41
Subject: Re: in() VS exists()

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group