Select query, difference in dates with multiple record on MS Access

51 Views Asked by Ibrahim Gurhan At 17 December 2017 at 19:46

I am working on a database, but having trouble with something(total noob). I can't post the images yet on this account, I have posted links to my images. Basically, I have a reservation table and a room table. I wanted to make a Date Diff operation For each room, and multiply it by rent price, and group it by room number. The end result I was trying to make is like this, after I hide the date difference because I would only need it for the calculation.

<!DOCTYPE html>
<html>
<body>

<table style="width:100%">
  <tr>
    <th>Room Number</th>
    <th>Total</th> 
 
  </tr>
  <tr>
    <td>1</td>
    <td>375</td>
    
  </tr>
  <tr>
    <td>2</td>
    <td>375</td>
    
  </tr>
  <tr>
    <td>3</td>
    <td>1680</td>
    
    
  </tr>
  <tr>
    <td>4</td>
    <td>700</td>
    
  </tr>
  <tr>
    <td>6</td>
    <td>60</td>
    
  </tr>
    <tr>
    <td>7</td>
    <td>540</td>
    
  </tr>
</table>

</body>
</html>

I have this as my SQL Query:

SELECT [Room Number], 
DATEDIFF("d", min(CheckInDate), max(CheckOutDate)) as dif
FROM Reservations
GROUP by [Room Number]

I did not do the calculation for money part yet, as I cant even make the Datediff work for each room.

Original Q&A

There are 2 best solutions below

DecoderReloaded On 17 December 2017 at 19:56

I think you need to calculate the datediff for each room-customer pair and then group by room_id. You can multiple the price afterwords or during the group by. I don't know ms-access but by sql logic it should look like:

SELECT [Room Number], SUM(DATEDIFF("d", CheckInDate, CheckOutDate)) * price_per_night as dif 
FROM Reservations 
GROUP by [Room Number]

Rene On 17 December 2017 at 20:51

Let's assume your bookings are stored in a table called Reservations and the room info is stored in a table called Rooms. If you want to include all rooms, whether they were booked or not, use this:

SELECT Rooms.[Room Number], Sum((Reservations.CheckOutDate-Reservations.CheckInDate)*Rooms.Price) AS [Total per room]
FROM Reservations RIGHT JOIN Rooms ON Reservations.[Room Number] = Rooms.[Room Number]
GROUP BY Rooms.[Room Number];

Replace RIGHT JOIN by INNER JOIN if you're only interested in rooms that were booked.

PS your HTML example is wrong for room 7, it should be 1080 (9*120).

Select query, difference in dates with multiple record on MS Access

There are 2 best solutions below

Related Questions in SQL

Related Questions in MS-ACCESS

Related Questions in SELECT-QUERY

Trending Questions

Popular # Hahtags

Popular Questions