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

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