2

I am working on an apartment rental website where companies have their accounts and can upload properties. I'm stuck with seasonal pricing implementation. Each property may have different rates in different periods of time during a year. I have the following tables:

objects

    drop table if exists objects;

    create table objects (
    id bigint unsigned not null primary key
    );

    insert into objects values('1200');
    insert into objects values('1201');
    insert into objects values('1202');
    insert into objects values('1203');

rates

    drop table if exists rates;
    create table rates
    (
        id serial,
        obj_id_sys bigint unsigned not null,
        season          varchar(100),
        start_date      date            references calendar(date_),
        end_date        date            references calendar(date_),
        rate            float
    );
    insert into rates values ('', '1203', 'default',    '0000-00-00',   '0000-00-00',   75.00);
    insert into rates values ('', '1203', 'Low',    '2014-06-01',   '2014-06-30',   100.00);
    insert into rates values ('', '1203', 'High',   '2014-07-01',   '2014-07-30',   150.00);
    insert into rates values ('', '1203', 'Peak',   '2014-09-01',   '2014-09-30',   200.00);

    insert into rates values ('', '1202', 'default',    '0000-00-00',   '0000-00-00',   85.00);
    insert into rates values ('', '1202', 'June',    '2014-06-01',   '2014-06-30',   75.00);
    insert into rates values ('', '1202', 'July',   '2014-07-01',   '2014-07-30',   90.00);
    insert into rates values ('', '1202', 'September',   '2014-09-01',   '2014-09-30',           105.00);

    insert into rates values ('', '1201', 'default',    '0000-00-00',   '0000-00-00',   92.00);
    insert into rates values ('', '1201', 'Low',    '2014-06-01',   '2014-07-15',   80.00);
    insert into rates values ('', '1201', 'High',   '2014-07-01',   '2014-08-30',   88.00);
    insert into rates values ('', '1201', 'Peak',   '2014-09-01',   '2014-09-30',   95.00);

    insert into rates values ('', '1200', 'default',    '0000-00-00',   '0000-00-00',   92.00);
    insert into rates values ('', '1200', 'Low',    '2014-06-01',   '2014-06-30',   80.00);
    insert into rates values ('', '1200', 'High',   '2014-07-01',   '2014-08-1',   88.00);
    insert into rates values ('', '1200', 'Peak',   '2014-09-01',   '2014-09-30',   95.00);

Properties information is kept in the objects table.

Values in obj_id_sys field in rates table correspond to id in objects.

I want to return objects with their rates for an arbitrary user-defined period, say 2014-07-01 — 2014-08-20.

If the booking period does not meet any of the seasonal prices, the 'default' season price should be used.

I am using a helper table calendar with all possible dates:

    drop procedure fill_calendar;

    delimiter $$
    create procedure fill_calendar(start_date date, end_date date)
    begin
      declare date_ date;
      set date_=start_date;
      while date_ <= end_date do
        insert into calendar values(date_);
        set date_ = adddate(date_, interval 1 day);
      end while;
    end $$
    delimiter ;

    drop table if exists calendar;
    create table calendar 
    ( 
        date_       date        primary key
    );

    call fill_calendar('2014-1-1', '2020-12-31');

The following query returns almost what I want, except for that the default rate is returned for every date, even when seasonal prices are available for that one resulting in having tho rates for dates which fall within a season: that one of the season and the default one.

    select
        coalesce(objects.id, r.obj_id_sys) as id,
        calendar.date_ as date_,
        r.season as season,
            r.rate as amount
            from calendar
                LEFT OUTER JOIN rates AS r
                on (calendar.date_ >= r.start_date and calendar.date_ <= r.end_date) or r.season='default'
            JOIN objects 
                    on objects.id = r.obj_id_sys
        WHERE calendar.date_ between '2014-07-29' and '2014-08-3'
    order by id, date_
        ;

How can I return default rates only for those dates for which no seasonal prices are available? So that each object has only one rate for one date and I just do a SUM(rate) and GROUP BY id at the end to get object id and total price.

Also, some properties are available only during the seasons specified in table rates. If the booking period falls outside of these, the property should not be returned. Is there a way to do all of it in a single query?

Edit: This approach is based on this post:Hotel Room Rates for different seasons

Edit 2 Perhaps it'll be better not to use a single 'default' row for a property at all, filling instead the date ranges table with missing ranges and make them 'default'. But this is another problem. I posted it here: How to find missing date ranges?

Community
  • 1
  • 1
Victor
  • 127
  • 6
  • +1 for full details however could you please provide the DDL and some data for the calendar table – Abhik Chakraborty May 29 '14 at 06:54
  • The **calendar** table just contains all dates from 2014-01-01 to 2020-12-31, that's all. 2014-01-01, 2014-01-02, 2014-01-03 .. 2020-12-31. The provided stored procedure precisely fills the calendar table this way. – Victor May 29 '14 at 07:03
  • I see you have a procedure for adding data in calendar – Abhik Chakraborty May 29 '14 at 07:04
  • This is bit complicated let me know if this is what you are looking at http://sqlfiddle.com/#!2/fc8774/5 ? If not let me know what would be the expected result. Else will add this as an answer ! – Abhik Chakraborty May 29 '14 at 07:47
  • I don't think this is not what we need. This renders all dates outside booking period. We want a result with all dates within a user-defined booking period and their respective rates with regard to season for each object. – Victor May 29 '14 at 08:01
  • hmm I thought you want to display default column as well when there is no seasonal price available. Could you please provide a sample output with the data that you provided which you are looking at. This will be easy to understand it. – Abhik Chakraborty May 29 '14 at 08:20
  • In your fiddle, when you run the **select** query from my original post, you can see that, for example, there are two rates for property id 1200: default and high. I need it to render the default one only when there is no 'high' or 'low' or any other seasonal rate for this object on this date. – Victor May 29 '14 at 08:54
  • yes I checked that the issue is you are filtering the data with a date range and for all the default its always 0000-00-00 so you will never get a default. – Abhik Chakraborty May 29 '14 at 09:10

0 Answers0