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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
