aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN (DEFAULT fn_system_date())
row_name	text	OUT
day1	numeric	OUT
day2	numeric	OUT
day3	numeric	OUT
day4	numeric	OUT
day5	numeric	OUT
day6	numeric	OUT
day7	numeric	OUT

Definition

with forecast as (
        select "date"-start_date+1 as aday, arr_total, dep_total, inh_total, occ_overall, occ_actual
        from sp_occupancy_forecast(start_date, start_date+6)
    )
    select 
        'Arrival' as row_name, 
        sum(arr_total) filter(where aday = 1) as day1,
        sum(arr_total) filter(where aday = 2) as day2,
        sum(arr_total) filter(where aday = 3) as day3,
        sum(arr_total) filter(where aday = 4) as day4,
        sum(arr_total) filter(where aday = 5) as day5,
        sum(arr_total) filter(where aday = 6) as day6,
        sum(arr_total) filter(where aday = 7) as day7
    from forecast    
    union all
    select 
        'Departure' as row_name, 
        sum(dep_total) filter(where aday = 1) as day1,
        sum(dep_total) filter(where aday = 2) as day2,
        sum(dep_total) filter(where aday = 3) as day3,
        sum(dep_total) filter(where aday = 4) as day4,
        sum(dep_total) filter(where aday = 5) as day5,
        sum(dep_total) filter(where aday = 6) as day6,
        sum(dep_total) filter(where aday = 7) as day7
    from forecast    
    union all
    select 
        'In-House' as row_name, 
        sum(inh_total) filter(where aday = 1) as day1,
        sum(inh_total) filter(where aday = 2) as day2,
        sum(inh_total) filter(where aday = 3) as day3,
        sum(inh_total) filter(where aday = 4) as day4,
        sum(inh_total) filter(where aday = 5) as day5,
        sum(inh_total) filter(where aday = 6) as day6,
        sum(inh_total) filter(where aday = 7) as day7
    from forecast   
    union all
    select 
        'OCC% (Overall)' as row_name, 
        sum(occ_overall) filter(where aday = 1) as day1,
        sum(occ_overall) filter(where aday = 2) as day2,
        sum(occ_overall) filter(where aday = 3) as day3,
        sum(occ_overall) filter(where aday = 4) as day4,
        sum(occ_overall) filter(where aday = 5) as day5,
        sum(occ_overall) filter(where aday = 6) as day6,
        sum(occ_overall) filter(where aday = 7) as day7
    from forecast   
    union all
    select 
        'OCC% (Actual)' as row_name, 
        sum(occ_actual) filter(where aday = 1) as day1,
        sum(occ_actual) filter(where aday = 2) as day2,
        sum(occ_actual) filter(where aday = 3) as day3,
        sum(occ_actual) filter(where aday = 4) as day4,
        sum(occ_actual) filter(where aday = 5) as day5,
        sum(occ_actual) filter(where aday = 6) as day6,
        sum(occ_actual) filter(where aday = 7) as day7
    from forecast

sp_seven_days_forecast

Parameters

Definition