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. --- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
