How to find difference between two dates in sql?-CodePudding

the question goes like...

display those (records) customers details who stayed more then 2 days in hotel.... table1 Rooms contains arrival and check out dates table2 Customer contains customer details. both are connected via a primary key that is in room id

example 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 should be like

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

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

CodePudding user response：

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;

CodePudding user response：

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_