From: Aarni Ruuhimäki <aarni@kymi.com>
To: pgsql-sql@postgresql.org
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 19:25:26 +0200
Message-ID: <200803141925.26039.aarni@kymi.com> (raw)
In-Reply-To: <47DAA330.8070409@sympatico.ca>
References: <200803132025.27821.aarni@kymi.com>
<200803141713.40589.aarni@kymi.com>
<47DAA330.8070409@sympatico.ca>
On Friday 14 March 2008 18:09, Frank Bax wrote:
> >
> > Is zero days/nights, ignored. Not even possible to insert in the
> > application. end_day must be greater than start_day.
>
> You should still consider rows that are "out of range" or "zero nights"
> in your test cases to make sure your report processes them correctly.
For the WHERE clause for Jan 08 (will be $date1 and $date2), getting there.
1. start_day before period_start, end_day = period_start #1
2. start_day before period_start, end_day in period #1
3. start_day before period_start, end_day = period_end #1
4. start_day = period_start, end_day in period #2
5. start_day in period, end_day in period #3
6. start_day = period_start, end_day = period_end #2
7. start_day in period, end_day = period_end #3
8. start_day in period, end_day after period_end #3
9. start_day = period_start, end_day after period_end #2
10. start_day before period_start, end_day after period_end #1
SELECT res_id, to_char(res_start_day, 'DD.MM.YYYY'), to_char(res_end_day,
'DD.MM.YYYY')
FROM product_res
WHERE
group_id = 1 AND res_start_day < '2008-01-01' AND res_end_day >= '2008-01-01'
# covers 1,2,3,10
OR
group_id = 1 AND res_start_day = '2008-01-01' AND res_end_day >= '2008-01-01'
# covers 4,6,9
OR
group_id = 1 AND res_start_day >= '2008-01-01' AND res_start_day <
'2008-01-31' AND res_end_day >= '2008-01-01'; # covers 5,7,8
(499 rows) not yet summing up or grouping by.
But is this getting too heavy ? I have three more (optional) parameters to
pass into the query, which narrow down the result. All values are stored also
in the product_res table.
1. Area/region ID from dropdown, populated by areas that have products
2. Company ID from dropdown, dynamically populated according to the optional
area selection with companies that have products in the selected area
3. Product ID from dropdown, dynamically populated by the optional company
selection with the selected company's products in the selected area
So the WHERE clause would go like:
group_id = 1 AND res_start_day < '$date1' AND res_end_day >= '$date1' [AND
region_id = $region_id] [AND company_id = $company_id] [AND product_id =
$product_id]
OR
group_id = 1 AND res_start_day = '$date1' AND res_end_day >= '$date1' [AND
region_id = $region_id] [AND company_id = $company_id] [AND product_id =
$product_id]
OR
group_id = 1 AND res_start_day >= '$date1' AND res_start_day < '$date2' AND
res_end_day >= '$date1' [AND region_id = $region_id] [AND company_id =
$company_id] [AND product_id = $product_id]
Cheerio,
--
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
Subject: Re: Counting days ...
In-Reply-To: <200803141925.26039.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