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'))


[normalblue]

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

มันจะไม่มีประโยชน์เลยสำหรับข้อมูลชุดอื่นครับ... เวลาเราเขียน SQL Command ต้องพึงนึกถึงคำว่า Robust ไว้จนชินครับ เพราะหากมัน Robust แล้ว มัน Reusable ด้วย
ใช้ได้กับทุก Possibility ของข้อมูล

ถ้าหากคุณคิดว่า Constraint ของคุณกำหนดด้วย PHP ได้ไม่ขาดตกบกพร่อง ไม่ได้ปล่อยค่าว่างเข้ามา Search ใช้ของคุณ MapTwoZa ด้านบนครับ
ถ้าหากคุณคิดจะป้องกันตั้งแต่ PHP มา Database ก็เสริมอีกชั้น ก็กำหนดให้ครอบคลุมอีกรอบดักจับหากค่าใดค่าหนึ่งว่าง ใช้ของผมข้างบนครับ

ผมไม่เชื่อว่านอกจากตาราง Booking/Reservation แล้ว จะไม่มีตาราง Room หากไม่มีจริงๆ ก็เพิ่มเถอะครับ หากไม่อยากปวดหัวในอนาคต

สุดท้าย ต้องบอกว่าการออกแบบระบบจอง ค่อนข้างง่าย เมื่อเที่ยบกับ Logic ในแขนงอื่นๆ ครับ เช่น SCM, CRM เป็นต้น ออกแบบมาดี ตอนเทสก็ไม่ลำบากมาก ทำไปเรื่อยๆ เดี๋ยวมันก็ชินเองครับ

[seowizard]

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

มันจะไม่มีประโยชน์เลยสำหรับข้อมูลชุดอื่นครับ... เวลาเราเขียน SQL Command ต้องพึงนึกถึงคำว่า Robust ไว้จนชินครับ เพราะหากมัน Robust แล้ว มัน Reusable ด้วย
ใช้ได้กับทุก Possibility ของข้อมูล

ถ้าหากคุณคิดว่า Constraint ของคุณกำหนดด้วย PHP ได้ไม่ขาดตกบกพร่อง ไม่ได้ปล่อยค่าว่างเข้ามา Search ใช้ของคุณ MapTwoZa ด้านบนครับ
ถ้าหากคุณคิดจะป้องกันตั้งแต่ PHP มา Database ก็เสริมอีกชั้น ก็กำหนดให้ครอบคลุมอีกรอบดักจับหากค่าใดค่าหนึ่งว่าง ใช้ของผมข้างบนครับ

ผมไม่เชื่อว่านอกจากตาราง Booking/Reservation แล้ว จะไม่มีตาราง Room หากไม่มีจริงๆ ก็เพิ่มเถอะครับ หากไม่อยากปวดหัวในอนาคต

สุดท้าย ต้องบอกว่าการออกแบบระบบจอง ค่อนข้างง่าย เมื่อเที่ยบกับ Logic ในแขนงอื่นๆ ครับ เช่น SCM, CRM เป็นต้น ออกแบบมาดี ตอนเทสก็ไม่ลำบากมาก ทำไปเรื่อยๆ เดี๋ยวมันก็ชินเองครับ

ครับ  ผมวิเคราะห์แบบบ้าน ๆ อย่าถือสาเลย อิอิ 

[normalblue]

ครับ  ผมวิเคราะห์แบบบ้าน ๆ อย่าถือสาเลย อิอิ 

ครับ ช่วยๆ กันตอบ Braistorm กัน ความคิดเห็นจะได้หลากหลาย คนฟังจะได้ดูมีทางเลือก บ้านก็อบอุ่นครับ 

[p44n]

พวกท่านทำไมคิดกัน ให้ยุ่งยากขนาดนั้นคับ ไม่ปวดหัวเหรอ