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

Re: Counting days ...

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Cc: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
Subject: Re: Counting days ...
Date: 2008-03-13 20:09:33
Message-ID: 200803132209.33914.aarni@kymi.com (view raw or flat)
Thread:
Lists: pgsql-sql
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.
---

In response to

Responses

pgsql-sql by date

Next:From: Steve CrawfordDate: 2008-03-13 20:22:41
Subject: Re: Counting days ...
Previous:From: Steve CrawfordDate: 2008-03-13 18:58:46
Subject: Re: Counting days ...

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