From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni@kymi.com>
Reply-To: aarni@kymi.com
Organization: KYMI.com
To: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 15:04:54 +0200
User-Agent: KMail/1.6.2
References: <200803132025.27821.aarni@kymi.com>
	<200803132209.33914.aarni@kymi.com>
	<47D98D11.4080406@pinpointresearch.com>
In-Reply-To: <47D98D11.4080406@pinpointresearch.com>
Cc: Steve Crawford <scrawford@pinpointresearch.com>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200803141504.54211.aarni@kymi.com>

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

Thanks Steve,

I'm not sure if I quite grasped this. It gives a bit funny results:

SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) -=20
date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS=20
days_in_period,
c.country_name AS country
=46ROM product_res pr
LEFT JOIN countries c ON pr.country_id =3D c.country_id
WHERE group_id =3D 1 AND res_end_day >=3D '2007-01-01' AND res_end_day <=3D=
=20
'2008-12-31' group by pr.country_id, c.country_name;
 days_in_period |      country
=2D---------------+--------------------
        -441137 |
            -30 | Germany
            -28 | Estonia
             60 | Bulgaria
         -25003 | Russian Federation
        -207670 | Suomi
            256 | Ukraine
          -6566 | Latvia
           -280 | United States
          -1889 | Switzerland
            114 | Lithuania
             36 | Norway
            -66 | Sweden
            170 | Kazakhstan
             72 | Belarus
(15 rows)

Anyway, I have to rethink and elaborate the query. I know that it will usua=
lly=20
be on a monthly or yearly basis, but a reservation can actually be any of t=
he=20
following in relation to the given (arbitrary) period:

1. start_day before period_start, end_day =3D period_start

2. start_day before period_start, end_day in period

3. start_day before period_start, end_day =3D period_end

4. start_day =3D period_start, end_day in period

5. start_day in period, end_day in period

6. start_day =3D period_start, end_day =3D period_end

7. start_day in period, end_day =3D period_end

8. start_day in period, end_day after period_end

9. start_day =3D period_start, end_day =3D period_end

10 start_day before period_start, end_day after period_end

Hmm ...

Best regards,
=2D-=20
Aarni Ruuhim=E4ki
=2D--
Burglars usually come in through your windows.
=2D--