Re: Counting days ...

This was superfast, thank you !

On Thursday 13 March 2008 20:58, Steve Crawford wrote:
> Aarni Ruuhimäki wrote:

> > res_id 2, start_day 2008-02-10, end_day 2008-02-15, number of persons 4
> >
>
> 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.
>

Sorry, my bad, I meant 5 days x 4 persons = 20 person-days. From 10 to 15 it
is 5 'days' ? Arrival 10th and departure 15th makes 5 'nights' or 'days',
whatever one might call it, statistical accommodation units.

Likewise, res_id x, start_day some time before Feb 08, end_day 2008-02-16 for
a statistical period Feb 08 makes 16 units ?

>
> 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;

Ok, thanks, I'll dig into this.

>
> Add where-clauses to either for efficiency.

AND region_id = x<if>IsDefined(company_id), AND company_id = x</if>
<if>IsDefined(product_id), AND product_id = x</if>
>
> Cheers,
> Steve

days_in_period | country
----------------+--------------------
5519 | Unknown
16 | Germany
18 | Estonia
3061 | Russian Federation
1491 | Suomi
20 | Ukraine
(6 rows)

Getting there.

Cheers to all you amazingly helpful folks out there,

--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---