Question 21428 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find difference between two dates in sql? – Mysql

How can I display those records / customers details who stayed more then 2 days in hotel?
Table Rooms contains arrival and check out dates
Table Customer contains customer details. Both are connected via a primary key that is the room id.

Rooms

arrival checkout roomid
2/2/2004 2/3/2004 01
2/2/2004 2/3/2004 02

Customer

cust name room id
raj 01
rohan 02

Output

arrival checkout room id cust name
2/2/2004 2/3/2004 02 rohan

(ordering not mandatory, cust name can be 1st too)

Tags:

2

Answers


  1. 0

    A simple inner join can do the trick :

        select r.*, c.customerName
    from rooms r
    inner join customer c on c.roomid = r.roomid
    where datediff(checkout, arrival) > 1

    Try it here : https://dbfiddle.uk/dOnB7W9_

    Login or Signup to reply.
  2. 0

    It’s a bit weird that you have one customer per room (and not per room and time), but ok.

    SELECT
  arrival,
  checkout,
  DATEDIFF(checkout, arrival) "number of days", -- leave this line out
  rooms.roomid "room id",
  customers.name "cust name"
FROM rooms
LEFT JOIN customers ON rooms.roomid = customers.roomid
WHERE DATEDIFF(checkout, arrival) >= 2;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search