Message-ID: <47D98D11.4080406@pinpointresearch.com>
Date: Thu, 13 Mar 2008 13:22:41 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Thunderbird 2.0.0.12 (X11/20080227)
MIME-Version: 1.0
To: aarni@kymi.com
CC: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
References: <200803132025.27821.aarni@kymi.com>
	<47D97966.3030203@pinpointresearch.com>
	<200803132209.33914.aarni@kymi.com>
In-Reply-To: <200803132209.33914.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


> 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;
>>
>>     
Check my work, but I think the sum part of the query simply becomes:

sum (
  (
  date_smaller(res_end_day, '2008-02-29'::date) -
  date_larger(res_start_day, '2008-01-31'::date)
  ) * group_size
)

Basically remove the "+1" so we don't include both start and end dates 
but move the start base back one day so anyone starting prior to Feb 1 
gets the extra day added.

Cheers,
Steve