aonestar.public

Parameters

Name	Type	Mode
start_date	date	IN (DEFAULT fn_system_date())
end_date	date	IN (DEFAULT fn_system_date())
revenue_calc_type	integer	IN (DEFAULT fn_revenue_calc_type())
code	text	OUT
country	text	OUT
is_other	boolean	OUT
rooms	integer	OUT
guests	integer	OUT
revenue	t_money	OUT
adr	t_money	OUT

Definition

with summ as (
        select coalesce(cr.code, ct.code, n.geocode, 'OTH')::text AS code, 
           coalesce(cr."name", ct."name", cn."name", 'Others') AS country, 
           (coalesce(cr.code, ct.code, n.geocode) is NULL) AS is_other, 
           sum(in_house_qty)::int AS rooms, 
           sum(adult)::int AS guests, 
           case revenue_calc_type
               when 1 then sum(coalesce(room_charge, room_rate))
               else sum(room_rate)   
           end::t_money AS revenue
           --IIF(sum(in_house_qty) > 0, sum(room_rate)*100.00/sum(in_house_qty), 0.0)::t_money AS adr
        from room_summary A
            LEFT JOIN country ct on ct.id = a.guest_country_id 
            LEFT JOIN country cr on cr.id = a.guest_country_res_id 
            LEFT JOIN nationality n on n.id = a.guest_nation_id
            LEFT JOIN country cn ON cn.code = n.geocode 
        where a.room_status = 'OCC' and date_ref between $1 and $2
        group by 1,2,3
        having sum(in_house_qty) > 0
    ), A as (
        select * from summ
        union all 
        select c.code, c.name, false, 0 , 0 , 0.0 from country c
    )
    Select a.code, a.country, is_other, sum(rooms), sum(guests), sum(revenue), 
        coalesce(fn_money_div(sum(revenue), sum(rooms)), 0.0) as adr --max(adr)
    from A
    group by 1,2,3
    order by 3,2

sp_productivity_by_geocode

Parameters

Definition