Skip site navigation (1) Skip section navigation (2)

Re: Counting days ...

From: Aarni Ruuhimäki <aarni(at)kymi(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: Counting days ...
Date: 2008-03-14 17:25:26
Message-ID: 200803141925.26039.aarni@kymi.com (view raw or flat)
Thread:
Lists: 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.
---

In response to

Responses

pgsql-sql by date

Next:From: Frank BaxDate: 2008-03-15 16:05:17
Subject: Re: Counting days ...
Previous:From: PostgreSQL AdminDate: 2008-03-14 16:45:28
Subject: DB Design

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group