user3675188 user3675188 - 4 months ago 10
SQL Question

How to filter available rooms in specific period

Each row in the below table means

status of a room on a specific date


How could I write a query to filter rooms which are available within 2016-07-09 - 2016-07-19.

For example, for the
room_id
23, whose
saleable
state should be true between 2016-07-09 and 2016-07-19`. If so, pick it for the result.

Current SQL statement:

SELECT "room_skus"."id" AS t0_r0, "room_skus"."room_id" AS t0_r1, "room_skus"."price" AS t0_r2, "room_skus"."date" AS t0_r3, "room_skus"."created_at" AS t0_r4, "room_skus"."updated_at" AS t0_r5, "room_skus"."saleable" AS t0_r6, "room_skus"."annotation" AS t0_r7, "room_skus"."state" AS t0_r8, "rooms"."id" AS t1_r0, "rooms"."hotel_id" AS t1_r1, "rooms"."name" AS t1_r2, "rooms"."guests" AS t1_r3, "rooms"."created_at" AS t1_r4, "rooms"."updated_at" AS t1_r5, "rooms"."english_name" AS t1_r6, "hotels"."id" AS t2_r0, "hotels"."name" AS t2_r1, "hotels"."introduction" AS t2_r2, "hotels"."city_id" AS t2_r3, "hotels"."created_at" AS t2_r4, "hotels"."updated_at" AS t2_r5, "hotels"."checkin_time" AS t2_r6, "hotels"."checkout_time" AS t2_r7, "hotels"."region" AS t2_r8, "hotels"."english_name" AS t2_r9, "hotels"."address" AS t2_r10, "hotels"."official_website_link" AS t2_r11, "cities"."id" AS t3_r0, "cities"."country_id" AS t3_r1, "cities"."name" AS t3_r2, "cities"."created_at" AS t3_r3, "cities"."updated_at" AS t3_r4, "cities"."english_name" AS t3_r5, "cities"."code" AS t3_r6
FROM "room_skus" LEFT OUTER JOIN "rooms" ON "rooms"."id" = "room_skus"."room_id" LEFT OUTER JOIN "hotels" ON "hotels"."id" = "rooms"."hotel_id" LEFT OUTER JOIN "cities" ON "cities"."id" = "hotels"."city_id"
WHERE ("room_skus"."date" BETWEEN '2016-07-10 00:00:00.000000' AND '2016-07-30 23:59:59.999999') AND "cities"."id" = 1


The point is that I don't know how to write a SQL stmt to make it like
WHERE (room_skus.date BETWEEN '2016-07-09 00:00:00.000000' AND '2016-07-19 23:59:59.999999')
AND
saleable
state should be true on each
room_sku.id
for the continuing days period from 2016-07-09 to 2016-07-19.

Example data



+-----+---------+-------+------------+-------------------------+-------------------------+----------+------------+---------+
| id | room_id | price | date | created_at | updated_at | saleable | annotation | state |
+-----+---------+-------+------------+-------------------------+-------------------------+----------+------------+---------+
| 267 | 23 | 1500 | 2016-07-11 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 268 | 23 | 2000 | 2016-07-12 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 269 | 23 | 1500 | 2016-07-13 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 270 | 23 | 2000 | 2016-07-14 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 294 | 21 | 2000 | 2016-07-14 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 271 | 23 | 1500 | 2016-07-15 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 295 | 21 | 1500 | 2016-07-15 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 296 | 21 | 2500 | 2016-07-16 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 345 | 29 | 2500 | 2016-07-16 | 2016-07-06 18:19:22 UTC | 2016-07-06 18:19:22 UTC | false | | pending |
| 272 | 23 | 1500 | 2016-07-16 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 16 | 5 | 2500 | 2016-07-16 | 2016-07-06 18:19:18 UTC | 2016-07-06 18:19:18 UTC | false | | pending |
| 17 | 5 | 1500 | 2016-07-17 | 2016-07-06 18:19:18 UTC | 2016-07-06 18:19:18 UTC | false | | pending |
| 297 | 21 | 1500 | 2016-07-17 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 346 | 29 | 1000 | 2016-07-17 | 2016-07-06 18:19:22 UTC | 2016-07-06 18:19:22 UTC | false | | pending |
| 46 | 3 | 2500 | 2016-07-17 | 2016-07-06 18:19:19 UTC | 2016-07-06 18:19:19 UTC | false | | pending |
| 273 | 23 | 1500 | 2016-07-17 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 274 | 23 | 1000 | 2016-07-18 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |
| 298 | 21 | 2000 | 2016-07-18 | 2016-07-06 18:19:21 UTC | 2016-07-06 18:19:21 UTC | false | | pending |

Answer

Since you are not providing complete information I am going to assume:

  • You have a table room with 1 row per room.
  • You have a table room_skus with one row per room and day (complete set for the relevant time range).
  • The column saleable is boolean NOT NULL and date is defined date NOT NULL

Then you just pick all rooms that don't have a single day with saleable = FALSE in the requested time period:

SELECT id
FROM   room r
WHERE  NOT EXISTS (
   SELECT 1
   FROM   room_skus
   WHERE  date BETWEEN '2016-07-09' AND '2016-07-19'
   AND    room_id = r.id
   AND    NOT saleable
   );