1

Here my table structure:

___Segmentations:

|--------|------------|
| SEG_Id | SEG_Text   |
|--------|------------|
| 1      | Direct     |
| 2      | Website    |
| 3      | Other      |
|--------|------------|

___Bookings:

|--------|------------------|
| BOO_Id | BOO_Segmentation |
|--------|------------------|
| 1      | 1                |
| 2      | 1                |
| 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-25 | Night      |
| 6      | 2             | 2017-02-26 | Night      |
| 7      | 3             | 2017-02-28 | Night      |
| 8      | 3             | 2017-03-01 | Night      |
| 9      | 3             | 2017-03-02 | Night      |
| 10     | 3             | 2017-03-03 | Night      |
|--------|---------------|------------|------------|

I would like to know the most popular segmentation for a range of date.

The desired result should be this one for the following date range :

Form 2017-02-01 to 2017-02-28 inclusive

|------------|------------|------------|--------------|------------|
| ROO_Name   | Night_Nb   | Percentage | Booking_Nb   | Percentage |
|------------|------------|------------|--------------|------------|
| Direct     | 6          | 85.71      | 2            | 66.66      |
| Website    | 1          | 14.28      | 1            | 33.33      |
| Other      | 0          | 0          | 0            | 0          |
|------------|------------|------------|--------------|------------|

What I already tried:

SELECT r.SEG_Id
     , Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) Night_Nb
     , Concat(
         Format(
           Sum(CASE WHEN BOO_Id IS NULL THEN 0 ELSE 1 END) 
           / TotalBookings 
           * 100
         , 0) ) AS PercentageTotal
  FROM (  ___Segmentations r LEFT JOIN ___Bookings b ON r.SEG_Id = b.BOO_Segmentation
       ) INNER JOIN (SELECT BOO_HotelId
                          , Count(*) AS TotalBookings
                       FROM ___Bookings 
                      GROUP BY BOO_HotelId
                    ) AS TotalHotelBookings 
                 ON r.SEG_HotelId = TotalHotelBookings.BOO_HotelId
 WHERE r.SEG_HotelId = :hotel_id
 GROUP BY r.SEG_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/1aa10a

1
select seg.seg_text,
       ifnull(nights.Night_Nb,0) as Night_Nb,
       ifnull(nights.Percentage,0) as Percentage,
       ifnull(bookings.Booking_Nb,0) as Booking_Nb,
       ifnull(bookings.Percentage,0) as Percentage
  from __Segmentations seg
 left join ( select seg.seg_id,
                    count(*) as Night_Nb,
                    round(100*count(*)/(select count(*) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as nights  on (nights.seg_id=seg.seg_id)
 left join ( select seg.seg_id,
                    count(distinct bil.bil_bookingid) as Booking_Nb,
                    round(100*count(distinct bil.bil_bookingid)/(select count(distinct bil_bookingid) from __BillableDatas where bil_date between '2017-02-01' and '2017-02-28'),2) as Percentage
               from __Segmentations seg
               join __Bookings boo on (boo.boo_segmentation=seg.seg_id)
               join __BillableDatas bil on (bil.bil_bookingid=boo.boo_id)
              where bil_date between '2017-02-01' and '2017-02-28'
              group by seg.seg_id ) as bookings  on (bookings.seg_id=seg.seg_id)
 group by seg.seg_id;
+----------+----------+------------+------------+------------+
| seg_text | Night_Nb | Percentage | Booking_Nb | Percentage |
+----------+----------+------------+------------+------------+
| Direct   |        6 |      85.71 |          2 |      66.67 |
| Website  |        1 |      14.29 |          1 |      33.33 |
| Other    |        0 |       0.00 |          0 |       0.00 |
+----------+----------+------------+------------+------------+
3 rows in set (0.02 sec)
|improve this answer|||||
0

First, you need to fetch all records that match the specifications

select      *
from        ___BillableDatas bd
inner join  ___Bookings bk
on          bd.BIL_BookingId = bk.BOO_Id
inner join  ___Segmentations sg
on          sg.SEG_Id = BOO_Segmentation
where       bd.BIL_Date between '2017-02-01' and '2017-02-28'
group by    sg.SEG_Id;

This is it:

| BIL_Id | BIL_HotelId | BIL_BookingId | BIL_Date    | BIL_Item | SEG_Id | SEG_Text |
|--------|-------------|---------------|-------------|----------|--------|----------|
| 1      | AAA00       | 1             | 21.02.2017  | Night    | 1      | Direct   |
| 2      | AAA00       | 1             | 22.02.2017  | Night    | 1      | Direct   |
| 3      | AAA00       | 1             | 23.02.2017  | Night    | 1      | Direct   |
| 4      | AAA00       | 1             | 24.02.2017  | Night    | 1      | Direct   |
| 5      | AAA00       | 2             | 25.02.2017  | Night    | 1      | Direct   |
| 6      | AAA00       | 2             | 26.02.2017  | Night    | 1      | Direct   |
| 7      | AAA00       | 3             | 28.02.2017  | Night    | 2      | Website  |

But, grouping by segmentation, get count of nights and count of different bookings.

(select      sg.SEG_Id,
             count(sg.SEG_Id) as Night_Nb,
             count(distinct bk.BOO_Id) as Booking_Nb
from        ___BillableDatas bd
inner join  ___Bookings bk
on          bd.BIL_BookingId = bk.BOO_Id
inner join  ___Segmentations sg
on          sg.SEG_Id = BOO_Segmentation
where       bd.BIL_Date between '2017-02-01' and '2017-02-28'
group by    sg.SEG_Id) agg

This returns next result:

| SEG_Id | Night_Nb | Booking_Nb |
|:------:|:--------:|:----------:|
|    1   |     6    |      2     |
|    2   |     1    |      1     |

Just to calculate percentages, you need overall count of nights and bookings:

select     count(sg.SEG_Id) as Total_Night_Nb,
           count(distinct bk.BOO_Id) as Total_Booking_Nb
from        ___BillableDatas bd
inner join  ___Bookings bk
on          bd.BIL_BookingId = bk.BOO_Id
inner join  ___Segmentations sg
on          sg.SEG_Id = BOO_Segmentation
where       bd.BIL_Date between '2017-02-01' and '2017-02-28'

| Total_Night_Nb | Total_Booking_Nb |
|:--------------:|:----------------:|
|        7       |         3        |

Put it all together.

As far as you want all segmentation, with and without result, you need all records from segmentation's table, LEFT JOIN count of nights and bookings grouped by segment, and CROSS JOIN total's subquery just to calculate percentage.

select      sg.SEG_Text,
            coalesce(agg.Night_Nb,0) Night_Nb,
            agg.Night_nb * 100 / agg2.Total_Night_Nb as Percent_Night_Nb,
            coalesce(agg.Booking_Nb, 0) Booking_Nb,
            agg.Booking_Nb * 100 / agg2.Total_Booking_Nb as Percent_Booking_Nb
from        ___Segmentations sg
left join   (select      sg.SEG_Id,
                         count(sg.SEG_Id) as Night_Nb,
                         count(distinct bk.BOO_Id) as Booking_Nb
             from        ___BillableDatas bd
             inner join  ___Bookings bk
             on          bd.BIL_BookingId = bk.BOO_Id
             inner join  ___Segmentations sg
             on          sg.SEG_Id = BOO_Segmentation
             where       bd.BIL_Date between '2017-02-01' and '2017-02-28'
             group by    sg.SEG_Id) agg
on           sg.SEG_Id = agg.SEG_Id
cross join   (select     count(sg.SEG_Id) as Total_Night_Nb,
                         count(distinct bk.BOO_Id) as Total_Booking_Nb
             from        ___BillableDatas bd
             inner join  ___Bookings bk
             on          bd.BIL_BookingId = bk.BOO_Id
             inner join  ___Segmentations sg
             on          sg.SEG_Id = BOO_Segmentation
             where       bd.BIL_Date between '2017-02-01' and '2017-02-28') agg2   
order by     agg.Night_Nb desc
;

Is this the desired result?

| SEG_Text | Night_Nb | Percent_Night_Nb | Booking_Nb | Percent_Booking_Nb |
|----------|:--------:|:----------------:|:----------:|:------------------:|
| Direct   |     6    |      85,7143     |      2     |       66,6667      |
| Website  |     1    |      14,2857     |      1     |       33,3333      |
| Other    |     0    |       NULL       |      0     |        NULL        |

You can check it here: http://rextester.com/FXBV42460

|improve this answer|||||

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.