From: Steve Crawford <scrawford@pinpointresearch.com>
To: aarni@kymi.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Thu, 13 Mar 2008 13:22:41 -0700
Message-ID: <47D98D11.4080406@pinpointresearch.com> (raw)
In-Reply-To: <200803132209.33914.aarni@kymi.com>
References: <200803132025.27821.aarni@kymi.com>
<47D97966.3030203@pinpointresearch.com>
<200803132209.33914.aarni@kymi.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: scrawford@pinpointresearch.com, aarni@kymi.com
Subject: Re: Counting days ...
In-Reply-To: <47D98D11.4080406@pinpointresearch.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox