aonestar.public

Parameters

Name	Type	Mode
a_period_type	text	IN (DEFAULT NULL)
a_start_date	date	IN (DEFAULT NULL)
a_end_date	date	IN (DEFAULT NULL)
b_period_type	text	IN (DEFAULT NULL)
b_start_date	date	IN (DEFAULT NULL)
b_end_date	date	IN (DEFAULT NULL)
segment	text	IN (DEFAULT 'month')
segment_group	text	IN (DEFAULT NULL)
sort_column	text	IN (DEFAULT NULL)
limit_rows	integer	IN (DEFAULT NULL)
filter_text	text	IN (DEFAULT NULL)
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
days	json	IN (DEFAULT NULL)

Definition

DECLARE
    v_a_start date; v_a_end date;
    v_b_start date; v_b_end date;
BEGIN
    -- Resolve both periods via fn_period
    SELECT p.start_date, p.end_date INTO v_a_start, v_a_end
    FROM fn_period(upper(a_period_type), a_start_date, a_end_date) p;

SELECT p.start_date, p.end_date INTO v_b_start, v_b_end
    FROM fn_period(upper(b_period_type), b_start_date, b_end_date) p;

RETURN (
        WITH pa AS (
            SELECT * FROM sp_rsv_booking_pace_stat(
                NULL, v_a_start, v_a_end,
                segment, segment_group, sort_column, 999999, filter_text, revenue_calc_type,
                days
            )
        ),
        pb AS (
            SELECT * FROM sp_rsv_booking_pace_stat(
                NULL, v_b_start, v_b_end,
                segment, segment_group, sort_column, 999999, filter_text, revenue_calc_type,
                days
            )
        ),
        merged AS (
            SELECT
                COALESCE(pa.group_id,   pb.group_id)   AS group_id,
                COALESCE(pa.group_name, pb.group_name) AS group_name,
                COALESCE(pa.id,         pb.id)         AS id,
                COALESCE(pa.name,       pb.name)       AS name,
                COALESCE(pb.bookings,    0) AS b_bookings,
                COALESCE(pb.room_nights, 0) AS b_room_nights,
                COALESCE(pb.revenue,     0) AS b_revenue,
                COALESCE(pa.bookings,    0) AS a_bookings,
                COALESCE(pa.room_nights, 0) AS a_room_nights,
                COALESCE(pa.revenue,     0) AS a_revenue
            FROM pa
            FULL OUTER JOIN pb
                ON  COALESCE(pa.id,       -1) = COALESCE(pb.id,       -1)
                AND COALESCE(pa.group_id, -1) = COALESCE(pb.group_id, -1)
        ),
        ranked AS (
            SELECT
                ROW_NUMBER() OVER (
                    PARTITION BY group_id
                    ORDER BY (id = 999999), id ASC NULLS LAST
                ) AS rn,
                *
            FROM merged
        ),
        limited AS (
            SELECT
                group_id, group_name,
                CASE WHEN id = 999999 OR rn > COALESCE(limit_rows, 2147483647) THEN 999    ELSE rn   END AS seq,
                CASE WHEN id = 999999 OR rn > COALESCE(limit_rows, 2147483647) THEN 999999 ELSE id   END AS id,
                CASE WHEN id = 999999 OR rn > COALESCE(limit_rows, 2147483647) THEN 'Others/Unknown' ELSE name END AS name,
                b_bookings, b_room_nights, b_revenue,
                a_bookings, a_room_nights, a_revenue
            FROM ranked
        ),
        agg AS (
            SELECT
                group_id, group_name, seq, id, name,
                SUM(b_bookings)::int           AS b_bookings,
                SUM(b_room_nights)::int        AS b_room_nights,
                SUM(b_revenue)::public.t_money AS b_revenue,
                SUM(a_bookings)::int           AS a_bookings,
                SUM(a_room_nights)::int        AS a_room_nights,
                SUM(a_revenue)::public.t_money AS a_revenue
            FROM limited
            GROUP BY group_id, group_name, seq, id, name
        )
        SELECT json_build_object(
            'period_a', json_build_object(
                'period_type', upper(a_period_type),
                'start_date',  v_a_start,
                'end_date',    v_a_end
            ),
            'period_b', json_build_object(
                'period_type', upper(b_period_type),
                'start_date',  v_b_start,
                'end_date',    v_b_end
            ),
            'rows', COALESCE(
                (
                    SELECT json_agg(
                        json_build_object(
                            'seq',             seq,
                            'group_id',        group_id,
                            'group_name',      group_name,
                            'id',              id,
                            'name',            name,
                            'b_bookings',      b_bookings,
                            'b_room_nights',   b_room_nights,
                            'b_revenue',       b_revenue,
                            'b_adr',           ROUND(b_revenue::numeric / NULLIF(b_room_nights, 0), 2),
                            'a_bookings',      a_bookings,
                            'a_room_nights',   a_room_nights,
                            'a_revenue',       a_revenue,
                            'a_adr',           ROUND(a_revenue::numeric / NULLIF(a_room_nights, 0), 2),
                            'pct_bookings',    ROUND((a_bookings    - b_bookings)   ::numeric / NULLIF(b_bookings,    0) * 100, 2),
                            'pct_room_nights', ROUND((a_room_nights - b_room_nights)::numeric / NULLIF(b_room_nights, 0) * 100, 2),
                            'pct_revenue',     ROUND((a_revenue     - b_revenue)    ::numeric / NULLIF(b_revenue,     0) * 100, 2),
                            'pct_adr',         ROUND(
                                (a_revenue::numeric / NULLIF(a_room_nights, 0) -
                                 b_revenue::numeric / NULLIF(b_room_nights, 0))
                                / NULLIF(b_revenue::numeric / NULLIF(b_room_nights, 0), 0) * 100, 2)
                        )
                        ORDER BY group_id NULLS FIRST, (seq = 999), seq
                    )
                    FROM agg
                ),
                '[]'::json
            )
        )
    );
END

sp_rsv_booking_pace_compare_js

Parameters

Definition