From: Aarni Ruuhimäki <aarni@kymi.com>
To: pgsql-sql@postgresql.org
Cc: Steve Crawford <scrawford@pinpointresearch.com>
Subject: Re: Counting days ...
Date: Fri, 14 Mar 2008 15:04:54 +0200
Message-ID: <200803141504.54211.aarni@kymi.com> (raw)
In-Reply-To: <47D98D11.4080406@pinpointresearch.com>
References: <200803132025.27821.aarni@kymi.com>
<200803132209.33914.aarni@kymi.com>
<47D98D11.4080406@pinpointresearch.com>
>
> Check my work, but I think the sum part of the query simply becomes:
>
> sum (
> (
> date_smaller(res_end_day, '2008-02-29'::date) -
> date_larger(res_start_day, '2008-01-31'::date)
> ) * group_size
> )
>
> Basically remove the "+1" so we don't include both start and end dates
> but move the start base back one day so anyone starting prior to Feb 1
> gets the extra day added.
>
> Cheers,
> Steve
Thanks Steve,
I'm not sure if I quite grasped this. It gives a bit funny results:
SELECT sum ((date_smaller(res_end_day, '2007-12-31'::date) -
date_larger(res_start_day, '2006-12-31'::date)) * group_size) AS
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 res_end_day >= '2007-01-01' AND res_end_day <=
'2008-12-31' group by pr.country_id, c.country_name;
days_in_period | country
----------------+--------------------
-441137 |
-30 | Germany
-28 | Estonia
60 | Bulgaria
-25003 | Russian Federation
-207670 | Suomi
256 | Ukraine
-6566 | Latvia
-280 | United States
-1889 | Switzerland
114 | Lithuania
36 | Norway
-66 | Sweden
170 | Kazakhstan
72 | Belarus
(15 rows)
Anyway, I have to rethink and elaborate the query. I know that it will usually
be on a monthly or yearly basis, but a reservation can actually be any of the
following in relation to the given (arbitrary) period:
1. start_day before period_start, end_day = period_start
2. start_day before period_start, end_day in period
3. start_day before period_start, end_day = period_end
4. start_day = period_start, end_day in period
5. start_day in period, end_day in period
6. start_day = period_start, end_day = period_end
7. start_day in period, end_day = period_end
8. start_day in period, end_day after period_end
9. start_day = period_start, end_day = period_end
10 start_day before period_start, end_day after period_end
Hmm ...
Best regards,
--
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, scrawford@pinpointresearch.com
Subject: Re: Counting days ...
In-Reply-To: <200803141504.54211.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