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

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.
---