aonestar.public

Parameters

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

Definition

WITH sys AS (
    SELECT fn_system_date() AS d
),
cur AS (
    SELECT
        id   AS day_id,
        COALESCE(SUM(new_bookings),   0) AS bookings,
        COALESCE(SUM(new_roomnights), 0) AS room_nights,
        COALESCE(SUM(new_revenue),    0) AS revenue
    FROM sp_rsv_pickup_stat(
        NULL,
        start_date        => (SELECT d - days FROM sys),
        end_date          => (SELECT d        FROM sys),
        segment           => 'date',
        limit_rows        => 999999,
        revenue_calc_type => $2
    )
    GROUP BY id
),
ly AS (
    SELECT
        id   AS day_id,
        COALESCE(SUM(new_bookings),   0) AS bookings,
        COALESCE(SUM(new_roomnights), 0) AS room_nights,
        COALESCE(SUM(new_revenue),    0) AS revenue
    FROM sp_rsv_pickup_stat(
        NULL,
        start_date        => (SELECT (d - days - INTERVAL '1 year')::date FROM sys),
        end_date          => (SELECT (d        - INTERVAL '1 year')::date FROM sys),
        segment           => 'date',
        limit_rows        => 999999,
        revenue_calc_type => $2
    )
    GROUP BY id
),
series AS (
    SELECT
        gs::date                                                         AS dt,
        to_char(gs::date, 'YYYYMMDD')::int                              AS day_id,
        to_char((gs::date - INTERVAL '1 year')::date, 'YYYYMMDD')::int  AS ly_day_id,
        CASE WHEN gs::date = (SELECT d FROM sys) THEN 'Today'
             ELSE (gs::date - (SELECT d FROM sys))::text
        END                                                              AS label
    FROM sys, generate_series((SELECT d - days FROM sys), (SELECT d FROM sys), '1 day') gs
)
SELECT COALESCE(
    json_agg(
        json_build_object(
            'label',          s.label,
            'date',           s.dt,
            'ly_date',        (s.dt - INTERVAL '1 year')::date,
            'bookings',       COALESCE(c.bookings,    0),
            'room_nights',    COALESCE(c.room_nights, 0),
            'revenue',        COALESCE(c.revenue,     0),
            'ly_bookings',    COALESCE(l.bookings,    0),
            'ly_room_nights', COALESCE(l.room_nights, 0),
            'ly_revenue',     COALESCE(l.revenue,     0)
        )
        ORDER BY s.dt
    ),
    '[]'::json
)
FROM series s
LEFT JOIN cur c ON c.day_id = s.day_id
LEFT JOIN ly  l ON l.day_id = s.ly_day_id

sp_rsv_booking_pace_daily_js

Parameters

Definition