On Saturday 15 March 2008 18:05, Frank Bax wrote:
> This is smaller; and should be equivalent:
>
> group_id = 1 AND
> ( res_start_day <= '$date1' AND res_end_day >= '$date1'
> OR
> res_start_day >= '$date1' AND res_start_day < '$date2' )
> [AND region_id = $region_id]
> [AND company_id = $company_id]
> [AND product_id = $product_id]
You're quite right.
Here's the whole thing in cfml as it is now. Explain analyze for year 2007
gives runtime 49.675 ms, which is not bad I think. The total page rendering
time to browser is 950 ms.
SELECT
SUM(
CASE
WHEN res_start_day < '#date1#' AND res_end_day = '#date1#' THEN (res_end_day -
(DATE '#date1#' - INTEGER '1'))
WHEN res_start_day < '#date1#' AND res_end_day >= '#date1#' AND res_end_day <=
'#date2#' THEN (res_end_day - (DATE '#date1#' - INTEGER '1'))
WHEN res_start_day < '#date1#' AND res_end_day = '#date2#' THEN (res_end_day -
(DATE '#date1#' - INTEGER '1'))
WHEN res_start_day = '#date1#' AND res_end_day >= '#date1#' AND res_end_day <=
'#date2#' THEN (res_end_day - '#date1#')
WHEN res_start_day >= '#date1#' AND res_start_day <= '#date2#' AND res_end_day
>= '#date1#' AND res_end_day <= '#date2#' THEN (res_end_day - res_start_day)
WHEN res_start_day >= '#date1#' AND res_start_day <= '#date2#' AND res_end_day
> '#date2#' THEN ('#date2#' - res_start_day)
WHEN res_start_day = '#date1#' AND res_end_day > '#date2#' THEN ('#date2#' -
res_start_day)
WHEN res_start_day < '#date1#' AND res_end_day > '#date2#' THEN ('#date2#' -
(DATE '#date1#' - INTEGER '1'))
END
* group_size) AS person_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 group_size > 0 AND res_start_day <= '#date1#' AND res_end_day
>= '#date1#' AND res_end_day > res_start_day
AND region_id = #form.region#
AND company_id = #form.companyt#
AND product_id = #form.product#
AND res_cancelled IS NOT TRUE
OR
group_id = 1 AND group_size > 0 AND res_start_day >= '#date1#' AND
res_start_day < '#date2#' AND res_end_day >= '#date1#' AND res_end_day >
res_start_day
AND region_id = #form.region#
AND company_id = #form.companyt#
AND product_id = #form.product#
AND res_cancelled IS NOT TRUE
group by pr.country_id, c.country_name;
Thank you guys again,
--
Aarni Ruuhimäki
---
Burglars usually come in through your windows.
---
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
