Message-ID: <47DAA0F5.3010503@pinpointresearch.com>
Date: Fri, 14 Mar 2008 08:59:49 -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>
	<200803132209.33914.aarni@kymi.com>
	<47D98D11.4080406@pinpointresearch.com>
	<200803141504.54211.aarni@kymi.com>
In-Reply-To: <200803141504.54211.aarni@kymi.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable

Aarni Ruuhim=E4ki wrote:
>
> 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
> FROM 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
> ----------------+--------------------
>         -441137 |
>             -30 | Germany
>             -28 | Estonia
> ...

I see one error in my logic. It doesn't account for the situation where=20
res_end_day is prior to the start of the period you are viewing. You can=20
fix this by limiting records with the appropriate where-clause or by=20
wrapping the date_smaller inside a date_larger (and vice-versa) to=20
ensure that all dates stay inside the desired period.

Or you can fix it by using an appropriate where-clause. Yours appears=20
broken - I think you want res_end_day >2006-12-31 (or >=3D2007-01-01 - I=20
prefer mine as you can use the same date in multiple places in the=20
query) which is what you have.

But I think you want the end of period to be limited to res_start_day=20
<=3D2007-12-31.

IOW, if your *end* date is *before* the period of interest or your=20
*start* date is *after* the period of interest, skip the record.

My guess is that you have records with res_start_day > 2007-12-31. After=20
applying the larger and smaller functions, this will end up with a=20
res_end_day of 2007-12-31 giving an end_day < start_day.

(I'm presuming you have appropriate constraints to prevent end_day from=20
being earlier than start_day. If not, check for that and add the=20
constraints.)

Cheers,
Steve