select value by checking each day of a given range in postgres

Question

I have a table (prop_rate_info) where specific day wise property and availability information been stored. like:

+-----------+---------------+---------------+----------------+----------+
| id        | property_id   | check_in_date | available_room | price    |
| (integer) | (integer)     | (date)        | (integer)      | (double) | 
+-----------+---------------+---------------+----------------+----------+
| 1         | 1             | 2018-07-20    | 2              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 2         | 1             | 2018-07-21    | 2              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 3         | 1             | 2018-07-23    | 2              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 4         | 2             | 2018-07-20    | 4              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 5         | 2             | 2018-07-21    | 1              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 6         | 2             | 2018-07-22    | 0              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 7         | 2             | 2018-07-23    | 4              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 8         | 3             | 2018-07-20    | 5              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 9         | 3             | 2018-07-21    | 5              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 10        | 3             | 2018-07-22    | 5              | 200      |
+-----------+---------------+---------------+----------------+----------+
| 11        | 3             | 2018-07-23    | 5              | 200      |
+-----------+---------------+---------------+----------------+----------+

I will give a date range and value for available room in integer. The query should search for each date of the range (from given range), if each day has available room value for a property, then it will give property id, otherwise it will ignore a property.

That means, if I give date range like 2018-07-20 to 2018-07-23 and available room value as 2, then it should check for each day (20th, 21st, 22nd, 23rd) whether it has available room of 2 for a property, if all day has that available room value, then it will give a property id.

This way it will give all property id.

I tried to use this query:

select property_id from prop_rate_info
where DATE(check_in_date) BETWEEN '2018-07-20' AND '2018-07-23' 
and available_room >= 2
group by property_id;

This is giving all property id, though table dont have rocord for all property for each day for those given range.

From the sample data given above, this should give property_id as 3, as it has all day's record also it has the available_room is more >= 2 for each day.

Can you please help on this, how this query can be done?

Edit your question and provide sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jul 29, 2018 at 13:33
Thanks @GordonLinoff , i updated the question with more detail information along with sample data. Please take a look. — Yousuf Zaman
– Yousuf Zaman, Commented Jul 29, 2018 at 13:57

Zaynul Abadin Tuhin · Accepted Answer · 2018-07-29 14:57:41Z

1

Just difference by from end to start date and use aggregate function

    select property_id from prop_rate_info
    where DATE(check_in_date) BETWEEN '2018-07-20' AND '2018-07-23' 
    and available_room >= 2
    group by property_id
    having count(*)=DATE_PART('day', '2018-07-23 00:00:00'::timestamp - '2018-07-20 00:00:00'::timestamp)+1

http://sqlfiddle.com/#!17/047ac/4

property_id
3

edited Jul 29, 2018 at 14:57

answered Jul 29, 2018 at 14:52

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yousuf Zaman Over a year ago

Thanks a lot @Zaynul Abedin Tuhin, It works perfectly as expected.

Collectives™ on Stack Overflow

select value by checking each day of a given range in postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related