aonestar
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
sp_productivity_stat_js
Parameters
Name
Type
Mode
overall
boolean
IN (DEFAULT true)
revenue_calc_type
integer
IN (DEFAULT fn_revenue_calc_type())
Definition
with today as ( select occ_rooms, total_revenue, iif($1, adr_overall, adr_actual) as ADR, iif($1, revpar_overall, revpar_actual) as RevPAR from sp_recapitulate('today', revenue_calc_type := revenue_calc_type) ), mtd as ( select occ_rooms, total_revenue, iif($1, adr_overall, adr_actual) as ADR, iif($1, revpar_overall, revpar_actual) as RevPAR from sp_recapitulate(from_date := fn_period_start('mtd'), to_date := fn_period_end('mtd')-1, revenue_calc_type := revenue_calc_type) ), ytd as ( select occ_rooms, total_revenue, iif($1, adr_overall, adr_actual) as ADR, iif($1, revpar_overall, revpar_actual) as RevPAR from sp_recapitulate(from_date := fn_period_start('ytd'), to_date := fn_period_end('ytd')-1, revenue_calc_type := revenue_calc_type) ), dataset as ( select json_build_object('today', today.occ_rooms, 'mtd', mtd.occ_rooms, 'ytd', ytd.occ_rooms) as room_night, json_build_object('today', today.total_revenue, 'mtd', mtd.total_revenue,'ytd', ytd.total_revenue) as revenue, json_build_object('today', today.ADR, 'mtd', mtd.ADR, 'ytd', ytd.ADR) as ADR, json_build_object('today', today.RevPAR, 'mtd', mtd.RevPAR, 'ytd', ytd.RevPAR) as RevPAR from today, mtd, ytd ) select row_to_json(dataset) from dataset;