Aarni Ruuhimäki 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) -
> date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS
> days_in_period,
> c.country_name AS country
> FROM product_res pr
> LEFT JOIN countries c ON pr.country_id = c.country_id
> WHERE group_id = 1 AND res_end_day >= '2007-01-01' AND res_end_day <=
> '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
res_end_day is prior to the start of the period you are viewing. You can
fix this by limiting records with the appropriate where-clause or by
wrapping the date_smaller inside a date_larger (and vice-versa) to
ensure that all dates stay inside the desired period.
Or you can fix it by using an appropriate where-clause. Yours appears
broken - I think you want res_end_day >2006-12-31 (or >=2007-01-01 - I
prefer mine as you can use the same date in multiple places in the
query) which is what you have.
But I think you want the end of period to be limited to res_start_day
<=2007-12-31.
IOW, if your *end* date is *before* the period of interest or your
*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
applying the larger and smaller functions, this will end up with a
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
being earlier than start_day. If not, check for that and add the
constraints.)
Cheers,
Steve