Re: Counting days ...
От | Aarni Ruuhimäki |
---|---|
Тема | Re: Counting days ... |
Дата | |
Msg-id | 200803151906.43271.aarni@kymi.com обсуждение исходный текст |
Ответ на | Re: Counting days ... (Frank Bax <fbax@sympatico.ca>) |
Список | 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. ---
В списке pgsql-sql по дате отправления: