Aarni Ruuhimäki wrote:
> 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';
>
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
persons and 15 days = 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
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
and end dates (assumed to be inclusive of both start and end) along with
group size and you want to see the person-days utilized within a
specified period.
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;
Add where-clauses to either for efficiency.
Cheers,
Steve
