Re: Counting days ... - Mailing list pgsql-sql

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
<cfif form.region GT 0>AND region_id = #form.region#</cfif>
<cfif form.company GT 0>AND company_id = #form.companyt#</cfif>
<cfif form.product GT 0>AND product_id = #form.product#</cfif>
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
<cfif form.region GT 0>AND region_id = #form.region#</cfif>
<cfif form.company GT 0>AND company_id = #form.companyt#</cfif>
<cfif form.product GT 0>AND product_id = #form.product#</cfif>
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.
---