SQL เชคเวลา

[@@@]

คำถามครับ

ต้องการใช้คำสัง SQL เลือกข้อมูลดูห้องว่าง ระหว่างวันที่ 2/05/2103 ถึง   4/05/2103

โดยที่ Table เก็บวัน checkinและcheckout เป็นคนละฟิว

ตาราสมมตินะครับ

ห้อง	checkin	checkout
A1	2103-05-01	2103-05-05
A2	2103-05-04	2103-05-08
A3	2103-05-05	2103-05-07

จะ เขียนคำสั่ง sql แบบไหนดีครับ
   

[adidog]

ลองศึกษาคำสั่ง between ดูครับ

[vanavat]

sql  between   


http://www.ninenik.com/%E0%B8%84%E0%B8%B3%E0%B8%AA%E0%B8%B1%E0%B9%88%E0%B8%87_SQL_BETWEEN-105.html

[@@@]

ถ้าเป็น วันที่ใน ฟิวเดียวกันไม่อยากครับ

แต่นี้มันคนละฟิวครับ

[Railway]

อยู่ที่ว่าเก็บข้อมูลเป็น DATE รึป่าว หรือเก็บเป็น Varchar เฉยๆ ถ้าเก็บเป็น DATE ก็เทียบเหมือนตัวเลขแหละครับ

[ouidesignweb]

SELECT * FROM ชื่อตาราง WHERE (checkin BETWEEN 2/05/2103 AND 4/05/2103) AND  (checkout BETWEEN 2/05/2103 AND 4/05/2103)

เดาล้วนๆ 

[@@@]

SELECT * FROM ชื่อตาราง WHERE (checkin BETWEEN 2/05/2103 AND 4/05/2103) AND  (checkout BETWEEN 2/05/2103 AND 4/05/2103)

เดาล้วนๆ 

แบบนี้ไม่ได้ครับ

เพราะ ถ้าวัน checkin เป็น 01/05/2103 และ checkout เป็น 6/05/2103

จะได้คำตอบว่าไม่มีข้อมูลซึ่งแปลว่าห้องว่าง แต่จริงๆแล้วถูกจองเต็มแล้วครับ

ขอบคุณที่มาตอบครับ

[normalblue]

ปัญหาของคำตอบคือต้องคิด Scenario ของเหตุการณ์ที่จะเกิดขึ้นให้ครบ โดยที่ หากช่วงเวลาระหว่าง Check-in -> Check-out มันไปซ้อนทับกับข้อมูลระยะเวลาในตารางต้อง Mark ไว้ว่า Reserved/Booked

ซึ่งกรณีที่จะเกิดการซ้อนทับ(Booked/Reserved) ก็มี Scenario ดังนี้ครับ
สมุมติ Checking in/out คือเวลาที่ใช้ค้นหา Checked in/out คือที่อยู่ในฐานข้อมูล
1. Checking In >= Checked In and Checking out <= Checked out // ช่วงเวลาที่กำลังค้นหา อยู่ภายในช่วงเวลาที่ห้องถูกจองไปแล้ว
2. Checking In <= Checked In and Checking out >= Checked out // ช่วงเวลาที่กำลังค้นหา ยาวครอบคลุมช่วงเวลาที่ห้องถูกจองไปแล้ว
1. Checking Out >= Checked In and Checking out <= Checked out // ช่วงเวลาที่กำลังค้นหา มีบางส่วนคาบเกี่ยวกับช่วงเวลาที่ห้องถูกจองไปแล้ว

พอกำหนด Scenario ครบก็เอามาเขียนเป็น SQL Statement ครับ

SELECT *
FROM [ROOM_TABLE]
WHERE roomId NOT IN
  (SELECT roomId
   FROM [RESERVATION_TABLE]
   WHERE
       (checkin <= '$checking_in' AND checkout >= '$checking_in') OR
       (checkin >= '$checking_in' AND checkout <= '$checking_out') OR
       (checkin <= '$checking_out' AND checkout >= '$checking_out'))

[MapTwoZa]

** start/stop คือ ช่วงวันที่ต้องการตรวจสอบ
เทียบ checkin กับ start/stop
จะแบ่งเวลาได้เป็น 3 ช่วง
1. checkin < start
2. checkin between start and stop
3. checkin > stop ตัดข้อนี้ทิ้งได้เลย ยังไงก็ไม่เข้าเงื่อนไข

ทีนี้ก็จะเหลือ 2 เงื่อนไขบน แต่ต้องเพิ่มเงื่อนไขเข้าไปนิดหน่อยให้มันเข้าครับ

1. checkin < start and chckout >= start
2. checkin between start and stop


เอา 2 เงื่อนไขข้างบนนี่ไปเขียนเป็น sql ก็ออกละครับ

[@@@]

ขอบคุณทุกท่านมากครับ +1 ทุกคน

เพิ่มเติม ถ้าใช้วิธีนี้จะได้เหมือนกันมั้ยครับ

SELECT * FROM TABLE   WHERE(begin_date <= '2103-05-07' AND end_date >= '2103-05-05')

[MapTwoZa]

select *
from table
where
(begin_date< date('2103-05-05') and end_date  >= date('2103-05-05'))
or
(begin_date between date('2103-05-05') and date('2103-05-07'))

[seowizard]

ซับซ้อนมาก บองต่ง ห้องว่างที่ว่า คือ checkin checkout ต้องเป็นค่าว่างใช่ไหม แล้วเราจะเอาฟิลด์ date จากไหนมาเช็คช่วงเวลาละทีนี้ อิอิ 

[cloudsphere]

ลองใช้ DateDiff ช่วยดูครับ
http://www.w3schools.com/sql/func_datediff.asp

[MapTwoZa]

โอ้ว พอคิดดีๆ รายละเอียดเยอะแฮะ

เช่น begin_date = 2103-05-05 แต่ end_date  = null แสดงว่ากำลังอยู่ก็ต้องเอามาคิดด้วย

ก็ต้องเอามาคิดด้วย
อืมมม ก็ต้องแก้ select นิดนึง

select
if(isnull(begin_date),date(0), date(begin_date)) as new_begin_date,
if(isnull(end_date) and isnull(begin_date), date(0), if(isnull(end_date),curdate(), date(end_date))) as new_end_date,
*
from table
where
(new_begin_date< date('2103-05-05') and new_end_date  >= date('2103-05-05'))
or
(new_begin_date between date('2103-05-05') and date('2103-05-07'))

[seowizard]

ถ้าโจทย์มีแค่ 3 ฟิลด์  เอาง่าย ๆเลยก็คิดหาเฉพาะวันที่ checkout ในช่วงเวลาดังกล่าวครับ นั่นหมายถึงช่วงเวลานั้นจะมีการ checkout ซึ่งแน่นอนมันจะว่าง

โค้ด เลือก ขยาย

SELECT room FROM table WHERE checkout BETWEEN 2013-05-02 AND 2013-05-04

[MapTwoZa]

ถ้าโจทย์มีแค่ 3 ฟิลด์  เอาง่าย ๆเลยก็คิดหาเฉพาะวันที่ checkout ในช่วงเวลาดังกล่าวครับ นั่นหมายถึงช่วงเวลานั้นจะมีการ checkout ซึ่งแน่นอนมันจะว่าง

โค้ด เลือก ขยาย

SELECT room FROM table WHERE checkout BETWEEN 2013-05-02 AND 2013-05-04

ไม่ได้ครับ ถ้า checkin วันที่ 1 แล้ว checkout วันที่ 5 ล่ะ
แล้วถ้า chcking วันที่ 1 แล้ว checkout null ละ

[seowizard]

ถ้าโจทย์มีแค่ 3 ฟิลด์  เอาง่าย ๆเลยก็คิดหาเฉพาะวันที่ checkout ในช่วงเวลาดังกล่าวครับ นั่นหมายถึงช่วงเวลานั้นจะมีการ checkout ซึ่งแน่นอนมันจะว่าง

โค้ด เลือก ขยาย

SELECT room FROM table WHERE checkout BETWEEN 2013-05-02 AND 2013-05-04

ไม่ได้ครับ ถ้า checkin วันที่ 1 แล้ว checkout วันที่ 5 ล่ะ
แล้วถ้า chcking วันที่ 1 แล้ว checkout null ละ

โจทย์เขาต้องการหาห้องว่างในช่วงเวลาดังกล่าว มันไม่มีตัวบ่งชี้ว่าอะไรที่บอกว่าห้องนั้นว่าง นอกจากเวลา check out  ครับ
(ถ้า checkin วันที่ 1 แล้ว checkout วันที่ 5 ก็ไม่อยู่ในเงื่อนไขครับ แสดงว่าห้องนั้นไม่ว่าง
ถ้ามี checkin แต่ checkout เป็น null ก็แสดงว่าห้องนั้นจองอยู่ หรือเป็น null ทั้งคู่ห้องนั้นก็คือห้องว่าง แต่มันอยู่ช่วงเวลาไหน)
เงื่อนไขที่เราสนใจคือ checkout ที่อยู่ระหว่างวันที่ 2-4  ให้แสดง room นั้นออกมา(อาจจะมี room ที่ยังไม่ว่างเสียทีเดียว เนื่องจากช่วงเวลาทาบเกี่ยวกันอยู่)
มันอาจจะไม่ตรงกับความต้องการเท่าไร แต่ถ้าโครงสร้างของ table มีฟิลด์ประมาณนี้คงต้องใช้วิธีนี้ละครับ   

[@@@]

เหมือนจะง่ายๆแต่คิดอยากเหมือนกันครับ

[seowizard]

ถ้าเป็นโปรแกรมที่เขียนขึ้นเองลอง Optimize Database ใหม่ครับ  ทำ Normalization เขียน ER ดี ๆ จะทำให้การ Query ง่ายขึ้น

[MapTwoZa]

#ไม่ว่าง

โค้ด เลือก ขยาย


select
if(isnull(begin_date),date(0), date(begin_date)) as new_begin_date,
if(isnull(end_date) and isnull(begin_date), date(0), if(isnull(end_date),curdate(), date(end_date))) as new_end_date,
*
from table
where
(new_begin_date< date('2103-05-05') and new_end_date  >= date('2103-05-05'))
or
(new_begin_date between date('2103-05-05') and date('2103-05-07'))


#ว่าง

โค้ด เลือก ขยาย


select
if(isnull(begin_date),date(0), date(begin_date)) as new_begin_date,
if(isnull(end_date) and isnull(begin_date), date(0), if(isnull(end_date),curdate(), date(end_date))) as new_end_date,
*
from table
where
not (new_begin_date< date('2103-05-05') and new_end_date  >= date('2103-05-05'))
and
not (new_begin_date between date('2103-05-05') and date('2103-05-07'))