aonestar.public

Parameters

Name	Type	Mode
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())

Definition

WITH sys AS (
    SELECT fn_system_date() AS d
),
by_date AS (
    SELECT
        id,
        new_bookings, new_roomnights, new_revenue,
        clx_bookings, clx_roomnights, clx_revenue,
        net_bookings, net_roomnights, net_revenue
    FROM sp_rsv_pickup_stat(
        NULL,
        start_date        => (SELECT d - 59 FROM sys),
        end_date          => (SELECT d FROM sys),
        segment           => 'date',
        limit_rows        => 999999,
        revenue_calc_type => $1
    )
),
thr AS (
    SELECT
        to_char(d - 2,  'YYYYMMDD')::int AS t3,
        to_char(d - 6,  'YYYYMMDD')::int AS t7,
        to_char(d - 13, 'YYYYMMDD')::int AS t14,
        to_char(d - 29, 'YYYYMMDD')::int AS t30,
        to_char(d - 59, 'YYYYMMDD')::int AS t60
    FROM sys
),
agg AS (
    SELECT
        -- last_3
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t3), 0)::int AS new_bq_3,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t3), 0)::int AS new_rn_3,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t3), 0)      AS new_rv_3,
        COALESCE(SUM(clx_bookings)   FILTER (WHERE id >= t3), 0)::int AS clx_bq_3,
        COALESCE(SUM(clx_roomnights) FILTER (WHERE id >= t3), 0)::int AS clx_rn_3,
        COALESCE(SUM(clx_revenue)    FILTER (WHERE id >= t3), 0)      AS clx_rv_3,
        COALESCE(SUM(net_bookings)   FILTER (WHERE id >= t3), 0)::int AS net_bq_3,
        COALESCE(SUM(net_roomnights) FILTER (WHERE id >= t3), 0)::int AS net_rn_3,
        COALESCE(SUM(net_revenue)    FILTER (WHERE id >= t3), 0)      AS net_rv_3,
        -- last_7
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t7), 0)::int AS new_bq_7,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t7), 0)::int AS new_rn_7,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t7), 0)      AS new_rv_7,
        COALESCE(SUM(clx_bookings)   FILTER (WHERE id >= t7), 0)::int AS clx_bq_7,
        COALESCE(SUM(clx_roomnights) FILTER (WHERE id >= t7), 0)::int AS clx_rn_7,
        COALESCE(SUM(clx_revenue)    FILTER (WHERE id >= t7), 0)      AS clx_rv_7,
        COALESCE(SUM(net_bookings)   FILTER (WHERE id >= t7), 0)::int AS net_bq_7,
        COALESCE(SUM(net_roomnights) FILTER (WHERE id >= t7), 0)::int AS net_rn_7,
        COALESCE(SUM(net_revenue)    FILTER (WHERE id >= t7), 0)      AS net_rv_7,
        -- last_14
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t14), 0)::int AS new_bq_14,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t14), 0)::int AS new_rn_14,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t14), 0)      AS new_rv_14,
        COALESCE(SUM(clx_bookings)   FILTER (WHERE id >= t14), 0)::int AS clx_bq_14,
        COALESCE(SUM(clx_roomnights) FILTER (WHERE id >= t14), 0)::int AS clx_rn_14,
        COALESCE(SUM(clx_revenue)    FILTER (WHERE id >= t14), 0)      AS clx_rv_14,
        COALESCE(SUM(net_bookings)   FILTER (WHERE id >= t14), 0)::int AS net_bq_14,
        COALESCE(SUM(net_roomnights) FILTER (WHERE id >= t14), 0)::int AS net_rn_14,
        COALESCE(SUM(net_revenue)    FILTER (WHERE id >= t14), 0)      AS net_rv_14,
        -- last_30
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t30), 0)::int AS new_bq_30,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t30), 0)::int AS new_rn_30,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t30), 0)      AS new_rv_30,
        COALESCE(SUM(clx_bookings)   FILTER (WHERE id >= t30), 0)::int AS clx_bq_30,
        COALESCE(SUM(clx_roomnights) FILTER (WHERE id >= t30), 0)::int AS clx_rn_30,
        COALESCE(SUM(clx_revenue)    FILTER (WHERE id >= t30), 0)      AS clx_rv_30,
        COALESCE(SUM(net_bookings)   FILTER (WHERE id >= t30), 0)::int AS net_bq_30,
        COALESCE(SUM(net_roomnights) FILTER (WHERE id >= t30), 0)::int AS net_rn_30,
        COALESCE(SUM(net_revenue)    FILTER (WHERE id >= t30), 0)      AS net_rv_30,
        -- last_60
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t60), 0)::int AS new_bq_60,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t60), 0)::int AS new_rn_60,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t60), 0)      AS new_rv_60,
        COALESCE(SUM(clx_bookings)   FILTER (WHERE id >= t60), 0)::int AS clx_bq_60,
        COALESCE(SUM(clx_roomnights) FILTER (WHERE id >= t60), 0)::int AS clx_rn_60,
        COALESCE(SUM(clx_revenue)    FILTER (WHERE id >= t60), 0)      AS clx_rv_60,
        COALESCE(SUM(net_bookings)   FILTER (WHERE id >= t60), 0)::int AS net_bq_60,
        COALESCE(SUM(net_roomnights) FILTER (WHERE id >= t60), 0)::int AS net_rn_60,
        COALESCE(SUM(net_revenue)    FILTER (WHERE id >= t60), 0)      AS net_rv_60
    FROM by_date CROSS JOIN thr
)
SELECT json_build_object(
    'new', json_build_array(
        json_build_object('period', 'last_3',  'label', 'Last 3 days',  'booking', new_bq_3,  'room_night', new_rn_3,  'revenue', new_rv_3,  'adr', ROUND(new_rv_3  / NULLIF(new_rn_3,  0), 2)),
        json_build_object('period', 'last_7',  'label', 'Last 7 days',  'booking', new_bq_7,  'room_night', new_rn_7,  'revenue', new_rv_7,  'adr', ROUND(new_rv_7  / NULLIF(new_rn_7,  0), 2)),
        json_build_object('period', 'last_14', 'label', 'Last 14 days', 'booking', new_bq_14, 'room_night', new_rn_14, 'revenue', new_rv_14, 'adr', ROUND(new_rv_14 / NULLIF(new_rn_14, 0), 2)),
        json_build_object('period', 'last_30', 'label', 'Last 30 days', 'booking', new_bq_30, 'room_night', new_rn_30, 'revenue', new_rv_30, 'adr', ROUND(new_rv_30 / NULLIF(new_rn_30, 0), 2)),
        json_build_object('period', 'last_60', 'label', 'Last 60 days', 'booking', new_bq_60, 'room_night', new_rn_60, 'revenue', new_rv_60, 'adr', ROUND(new_rv_60 / NULLIF(new_rn_60, 0), 2))
    ),
    'clx', json_build_array(
        json_build_object('period', 'last_3',  'label', 'Last 3 days',  'booking', clx_bq_3,  'room_night', clx_rn_3,  'revenue', clx_rv_3,  'adr', ROUND(clx_rv_3  / NULLIF(clx_rn_3,  0), 2)),
        json_build_object('period', 'last_7',  'label', 'Last 7 days',  'booking', clx_bq_7,  'room_night', clx_rn_7,  'revenue', clx_rv_7,  'adr', ROUND(clx_rv_7  / NULLIF(clx_rn_7,  0), 2)),
        json_build_object('period', 'last_14', 'label', 'Last 14 days', 'booking', clx_bq_14, 'room_night', clx_rn_14, 'revenue', clx_rv_14, 'adr', ROUND(clx_rv_14 / NULLIF(clx_rn_14, 0), 2)),
        json_build_object('period', 'last_30', 'label', 'Last 30 days', 'booking', clx_bq_30, 'room_night', clx_rn_30, 'revenue', clx_rv_30, 'adr', ROUND(clx_rv_30 / NULLIF(clx_rn_30, 0), 2)),
        json_build_object('period', 'last_60', 'label', 'Last 60 days', 'booking', clx_bq_60, 'room_night', clx_rn_60, 'revenue', clx_rv_60, 'adr', ROUND(clx_rv_60 / NULLIF(clx_rn_60, 0), 2))
    ),
    'net', json_build_array(
        json_build_object('period', 'last_3',  'label', 'Last 3 days',  'booking', net_bq_3,  'room_night', net_rn_3,  'revenue', net_rv_3,  'adr', ROUND(net_rv_3  / NULLIF(net_rn_3,  0), 2)),
        json_build_object('period', 'last_7',  'label', 'Last 7 days',  'booking', net_bq_7,  'room_night', net_rn_7,  'revenue', net_rv_7,  'adr', ROUND(net_rv_7  / NULLIF(net_rn_7,  0), 2)),
        json_build_object('period', 'last_14', 'label', 'Last 14 days', 'booking', net_bq_14, 'room_night', net_rn_14, 'revenue', net_rv_14, 'adr', ROUND(net_rv_14 / NULLIF(net_rn_14, 0), 2)),
        json_build_object('period', 'last_30', 'label', 'Last 30 days', 'booking', net_bq_30, 'room_night', net_rn_30, 'revenue', net_rv_30, 'adr', ROUND(net_rv_30 / NULLIF(net_rn_30, 0), 2)),
        json_build_object('period', 'last_60', 'label', 'Last 60 days', 'booking', net_bq_60, 'room_night', net_rn_60, 'revenue', net_rv_60, 'adr', ROUND(net_rv_60 / NULLIF(net_rn_60, 0), 2))
    )
)
FROM agg

sp_rsv_pickup_by_periods_detail_js

Parameters

Definition