aonestar.public

Parameters

Name	Type	Mode
date_ref	date	IN (DEFAULT fn_system_date())
day	integer	OUT
is_forecast	boolean	OUT
room_night	integer	OUT
revenue	t_money	OUT
room_night_lastyear	integer	OUT
revenue_lastyear	t_money	OUT

Definition

with this_year as (
        select adate, occ_rooms, revenue            
        from sp_get_occupancy_calendar(fn_month_start(date_ref), fn_month_end(date_ref)) occ 
    ), last_year as (
        select adate, occ_rooms, revenue           
        from sp_get_occupancy_calendar(
                fn_month_start((date_ref - interval'1 year')::date), 
                fn_month_end((date_ref - interval'1 year')::date)) occ 
    )
    select day_num as "day", 
        --max(adate) as "date", 
        (max(adate) > date_ref) as is_forecast,    
        sum(room_night) as room_night, 
        sum(revenue) as revenue, 
        sum(room_night_last) as room_night_last, 
        sum(revenue_last) as revenue_last
    from (
        select extract(day from adate) as day_num, adate, occ_rooms as room_night, revenue, null as room_night_last, null as revenue_last --, is_forecast
        from this_year 
        union all
        select extract(day from adate) as day_num, adate, null room_night, null revenue, occ_rooms as room_night_last, revenue as revenue_last 
        from last_year 
    ) A
    group by day_num
    order by day_num

sp_monthly_revenue

Parameters

Definition