2

Here my table structure:

___Rooms:

|--------|------------|
| ROO_Id | ROO_Name   |
|--------|------------|
| 1      | Room 1     |
| 2      | Room 2     |
| 3      | Room 3     |
|--------|------------|

___Bookings:

|--------|------------|
| BOO_Id | BOO_RoomId |
|--------|------------|
| 1      | 1          |
| 2      | 2          |
| 3      | 2          |
|--------|------------|

___BillableDatas:

|--------|---------------|------------|------------|
| BIL_Id | BIL_BookingId | BIL_Date   | BIL_Item   |
|--------|---------------|------------|------------|
| 1      | 1             | 2017-02-21 | Night      |
| 2      | 1             | 2017-02-22 | Night      |
| 3      | 1             | 2017-02-23 | Night      |
| 4      | 1             | 2017-02-24 | Night      |
| 5      | 2             | 2017-02-30 | Night      |
| 6      | 2             | 2017-02-31 | Night      |
| 7      | 1             | 2017-02-31 | Night      |
|--------|---------------|------------|------------|

I would like to know the most popular room.

The desired result should be:

|------------|------------|------------|
| ROO_Name   | Night Nb   | Percentage |
|------------|------------|------------|
| Room 1     | 5          | 71.42      |
| Room 2     | 2          | 28.57      |
| Room 3     | 0          | 0          |
|------------|------------|------------|

What I already tried:

SELECT r.ROO_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) NumBookings
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Rooms r LEFT JOIN ___Bookings b ON r.ROO_Id = b.BOO_RoomId
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.ROO_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.ROO_HotelId = :hotel_id
 GROUP BY r.ROO_Id
 ORDER BY NumBookings DESC

But it doesn't work actually.

Could anyone help me with this please ?

You could use the SQL Fiddle: http://sqlfiddle.com/#!9/fe0a77

2

I've changed the 'impossible dates' and I've set a rextester sample.

First group by RoomId and BookingId and calculate percentage.

(
 SELECT      b.BOO_RoomId, bd.BIL_BookingId, 
             COUNT(*) as NumNights,
             COUNT(*) * 100 / (SELECT COUNT(*) FROM ___BillableDatas) AS Percent 
 FROM        ___BillableDatas bd
 LEFT JOIN   ___Bookings B
 ON          b.BOO_Id = bd.BIL_BookingId
 GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
) co

Then LEFT JOIN with rooms table and show all values:

SELECT      r.ROO_Name,
            COALESCE(NumNights, 0) NumNights,
            COALESCE(Percent, 0) Percent
FROM        ___Rooms r
LEFT JOIN   (
            SELECT      b.BOO_RoomId, bd.BIL_BookingId, 
                        COUNT(*) as NumNights,
                        COUNT(*) * 100 / (SELECT COUNT(*) 
                                          FROM ___BillableDatas
                                          WHERE BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
                                          ) AS Percent 
            FROM        ___BillableDatas bd
            LEFT JOIN   ___Bookings B
            ON          b.BOO_Id = bd.BIL_BookingId
            WHERE       BIL_Date >= '2017-02-01' AND BIL_Date < '2017-02-23'
            GROUP BY    b.BOO_RoomId, bd.BIL_BookingId
            ) co
ON          R.ROO_Id = co.BOO_RoomId
;

+----------+---------------+-----------+---------+
| ROO_Name | BIL_BookingId | NumNights | Percent |
+----------+---------------+-----------+---------+
| Room 1   |       1       |     5     | 71,4286 |
| Room 2   |       2       |     2     | 28,5714 |
| Room 3   |       0       |     0     |  0,0000 |
+----------+---------------+-----------+---------+
|improve this answer|||||
  • Oh look perfect. last thing, how can I run this query but only for a dates range please? – F__M Feb 22 '17 at 21:12
  • Thanks so much for your help. I can't accept your solution like on StackOverflow ? – F__M Feb 22 '17 at 21:27
  • You should be able to accept it. – McNets Feb 22 '17 at 21:29
  • Strange, I can't see it... :( – F__M Feb 22 '17 at 21:37
  • @F__M maybe tomorrow ;) – McNets Feb 22 '17 at 21:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.