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.
---
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
