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
    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
        COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t3),  0)::int AS bq_3,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t3),  0)::int AS rn_3,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t3),  0)      AS rv_3,

COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t7),  0)::int AS bq_7,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t7),  0)::int AS rn_7,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t7),  0)      AS rv_7,

COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t14), 0)::int AS bq_14,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t14), 0)::int AS rn_14,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t14), 0)      AS rv_14,

COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t30), 0)::int AS bq_30,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t30), 0)::int AS rn_30,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t30), 0)      AS rv_30,

COALESCE(SUM(new_bookings)   FILTER (WHERE id >= t60), 0)::int AS bq_60,
        COALESCE(SUM(new_roomnights) FILTER (WHERE id >= t60), 0)::int AS rn_60,
        COALESCE(SUM(new_revenue)    FILTER (WHERE id >= t60), 0)      AS rv_60
    FROM by_date CROSS JOIN thr
)
SELECT json_build_array(
    json_build_object(
        'period', 'last_3',  'label', 'Last 3 days',
        'booking',    bq_3,  'room_night', rn_3,  'revenue', rv_3,
        'adr', ROUND(rv_3  / NULLIF(rn_3,  0), 2)
    ),
    json_build_object(
        'period', 'last_7',  'label', 'Last 7 days',
        'booking',    bq_7,  'room_night', rn_7,  'revenue', rv_7,
        'adr', ROUND(rv_7  / NULLIF(rn_7,  0), 2)
    ),
    json_build_object(
        'period', 'last_14', 'label', 'Last 14 days',
        'booking',    bq_14, 'room_night', rn_14, 'revenue', rv_14,
        'adr', ROUND(rv_14 / NULLIF(rn_14, 0), 2)
    ),
    json_build_object(
        'period', 'last_30', 'label', 'Last 30 days',
        'booking',    bq_30, 'room_night', rn_30, 'revenue', rv_30,
        'adr', ROUND(rv_30 / NULLIF(rn_30, 0), 2)
    ),
    json_build_object(
        'period', 'last_60', 'label', 'Last 60 days',
        'booking',    bq_60, 'room_night', rn_60, 'revenue', rv_60,
        'adr', ROUND(rv_60 / NULLIF(rn_60, 0), 2)
    )
)
FROM agg

sp_rsv_pickup_by_periods_js

Parameters

Definition