From: Aarni Ruuhimäki <aarni@kymi.com>
To: pgsql-sql@postgresql.org
Cc: Frank Bax <fbax@sympatico.ca>
Subject: Re: Counting days ...
Date: Sat, 15 Mar 2008 19:06:43 +0200
Message-ID: <200803151906.43271.aarni@kymi.com> (raw)
In-Reply-To: <47DBF3BD.9080306@sympatico.ca>
References: <200803132025.27821.aarni@kymi.com>
<200803141925.26039.aarni@kymi.com>
<47DBF3BD.9080306@sympatico.ca>
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.
---
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aarni@kymi.com, fbax@sympatico.ca
Subject: Re: Counting days ...
In-Reply-To: <200803151906.43271.aarni@kymi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox