CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.2
revoua
revoua

Reputation: 2059

find nonbreaking period with condition

There are quotas for hotels per day in a table. How to get number of days when hotel is daily available? 

q_id    q_hotel q_date  q_value
1   1   2013-02-01  1
2   1   2013-02-02  1
3   1   2013-02-03  1
4   1   2013-02-04  0
5   1   2013-02-05  2
6   1   2013-02-06  3
7   1   2013-02-07  3
8   1   2013-02-08  2
9   1   2013-02-09  0
10  1   2013-02-10  0
11  1   2013-02-11  1
12  1   2013-02-12  1

Wanted output: 

q_hotel q_date  days_available
1   2013-02-01  3
1   2013-02-02  2
1   2013-02-03  1
1   2013-02-04  0
1   2013-02-05  4
1   2013-02-06  3
1   2013-02-07  2
1   2013-02-08  1
1   2013-02-09  0
1   2013-02-10  0
1   2013-02-11  2
1   2013-02-12  1

For now I can get number of days if there is zero quote after needed date exists - I find closest unavailable day and calculate dates difference.
http://sqlfiddle.com/#!12/1a64c/14 

    select q_hotel
  ,q_date
  ,(select extract(day from (min(B.q_date)-A.q_date)) from Table1 B where B.q_date>A.q_date
   and B.q_value=0 and A.q_value<>0)
from Table1 A

But there is a problem when I don't have a zero closing date.

Upvotes: 2

Views: 111

Answers (1)

Tomasz Siorek
Tomasz Siorek

Reputation: 711

Here is a solution:

select
    a.q_date
,   a.q_hotel
,   case
        when
            a.q_value = 0
        then
            0
        else
        (
            select
                extract
                ( day from
                    min ( b.q_date ) - a.q_date + interval '1 day'
                )
            from    table1 b
            where   b.q_date >= a.q_date
            and     b.q_hotel = a.q_hotel
            and not exists
            (
                select  1
                from    table1 c
                where   c.q_date = b.q_date + interval '1 day'
                and     b.q_hotel = a.q_hotel
                and     q_value <> 0
            )
        )
    end as days_available
from    table1 a

Upvotes: 1

Related Questions