aonestar.public

Parameters

Name	Type	Mode
stat_date	date	IN (DEFAULT fn_system_date())
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())

Definition

WITH today AS (
    SELECT
        COALESCE(SUM(new_bookings),   0)::int  AS new_bq,
        COALESCE(SUM(clx_bookings),   0)::int  AS clx_bq,
        COALESCE(SUM(net_bookings),   0)::int  AS net_bq,
        COALESCE(SUM(new_roomnights), 0)::int  AS new_rn,
        COALESCE(SUM(clx_roomnights), 0)::int  AS clx_rn,
        COALESCE(SUM(net_roomnights), 0)::int  AS net_rn,
        COALESCE(SUM(nos_bookings),   0)::int  AS nos_bq,
        COALESCE(SUM(nos_roomnights), 0)::int  AS nos_rn,
        COALESCE(SUM(new_revenue),    0)       AS new_rev,
        COALESCE(SUM(clx_revenue),    0)       AS clx_rev,
        COALESCE(SUM(net_revenue),    0)       AS net_rev,
        (SUM(new_revenue) / NULLIF(SUM(new_roomnights), 0)) AS adr,
        MAX(alos)                              AS alos,
        MAX(alt)                               AS alt
    FROM sp_rsv_pickup_stat(
        NULL,
        start_date        => $1,
        end_date          => $1,
        segment           => 'date',
        revenue_calc_type => $2
    )
),
ly AS (
    SELECT
        COALESCE(SUM(new_bookings),   0)::int  AS new_bq,
        COALESCE(SUM(clx_bookings),   0)::int  AS clx_bq,
        COALESCE(SUM(net_bookings),   0)::int  AS net_bq,
        COALESCE(SUM(new_roomnights), 0)::int  AS new_rn,
        COALESCE(SUM(clx_roomnights), 0)::int  AS clx_rn,
        COALESCE(SUM(net_roomnights), 0)::int  AS net_rn,
        COALESCE(SUM(nos_bookings),   0)::int  AS nos_bq,
        COALESCE(SUM(nos_roomnights), 0)::int  AS nos_rn,
        COALESCE(SUM(new_revenue),    0)       AS new_rev,
        COALESCE(SUM(clx_revenue),    0)       AS clx_rev,
        COALESCE(SUM(net_revenue),    0)       AS net_rev,
        (SUM(new_revenue) / NULLIF(SUM(new_roomnights), 0)) AS adr,
        MAX(alos)                              AS alos,
        MAX(alt)                               AS alt
    FROM sp_rsv_pickup_stat(
        NULL,
        start_date        => ($1 - interval '1 year')::date,
        end_date          => ($1 - interval '1 year')::date,
        segment           => 'date',
        revenue_calc_type => $2
    )
)
SELECT json_build_object(
    'booking', json_build_object(
        'new',            t.new_bq,
        'clx',            t.clx_bq,
        'net',            t.net_bq,
        'last_year',      l.new_bq,
        'percent_change', ROUND((t.new_bq - l.new_bq)::numeric / NULLIF(l.new_bq, 0) * 100, 2)
    ),
    'room_night', json_build_object(
        'new',            t.new_rn,
        'clx',            t.clx_rn,
        'net',            t.net_rn,
        'last_year',      l.new_rn,
        'percent_change', ROUND((t.new_rn - l.new_rn)::numeric / NULLIF(l.new_rn, 0) * 100, 2)
    ),
    'no_show', json_build_object(
        'bookings',       t.nos_bq,
        'room_nights',    t.nos_rn,
        'last_year_bookings',   l.nos_bq,
        'last_year_room_nights', l.nos_rn
    ),
    'revenue', json_build_object(
        'new',            t.new_rev,
        'clx',            t.clx_rev,
        'net',            t.net_rev,
        'last_year',      l.new_rev,
        'percent_change', ROUND((t.new_rev - l.new_rev)::numeric / NULLIF(l.new_rev, 0) * 100, 2)
    ),
    'adr', json_build_object(
        'today',          t.adr,
        'last_year',      l.adr,
        'percent_change', ROUND((t.adr - l.adr)::numeric / NULLIF(l.adr, 0) * 100, 2)
    ),
    'alt', json_build_object(
        'today',          t.alt,
        'last_year',      l.alt,
        'percent_change', ROUND((t.alt - l.alt)::numeric / NULLIF(l.alt, 0) * 100, 2)
    ),
    'alos', json_build_object(
        'today',          t.alos,
        'last_year',      l.alos,
        'percent_change', ROUND((t.alos - l.alos)::numeric / NULLIF(l.alos, 0) * 100, 2)
    )
)
FROM today t, ly l

sp_rsv_pickup_stat_today_js

Parameters

Definition