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

Aarni Ruuhimäki wrote:
>> 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,



#6 and #9 are the same.  You missed these:

a    start_day before period_start, end_day before period_start
b    start_day = period_start, end_day = period_start
c    start_day = period_start, end_day after period_end
d    start_day = period_end, end_day = period_end
e    start_day = period_end, end_day after period_end
f    start_day after period_end, end_day after period_end

Granted, a & f should not match where clause; but then groups 10,c,e 
don't meet your where clause either.  Your where clause should probably be:

WHERE group_id = 1 AND (res_start_day >= '2007-01-01' AND res_end_day <= 
'2008-12-31')

Are you sure that your database does not have any rows where start_day 
is after end_day?  These rows could certainly skew results.

I would suggest that you identify a few rows that meet each of these 
conditions.  Change the where clause to select rows in one group at a 
time.  You might consider using a unique row identifier in where clause 
during these tests to make sure you are processing the rows you think 
you are.  When all test cases work properly; then run your generalized 
query again.