if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_get_guest_price]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_get_guest_price]
GO



CREATE function fn_get_guest_price (@date_begin datetime,@date_end datetime,@branch_no varchar(6))
returns decimal(16,2)
begin
	declare   @tal_money		numeric(16,2),
			  @tal_gmnum			numeric(16,2),
			  @avg_price			numeric(16,2)
		
	select @tal_gmnum = count(distinct s.flow_no),@tal_money = sum(sale_money) from t_rm_saleflow s with(nolock)
	where isnull((select top 1 card_id from t_rm_vip_acclist where t_rm_vip_acclist.flow_no = s.flow_no),'') <> '' and s.sell_way = 'A' and s.oper_date between @date_begin and @date_end and
		  s.branch_no like @branch_no 
	
	if isnull(@tal_gmnum,0) = 0
		set @avg_price = 0
	else
		set @avg_price = @tal_money / @tal_gmnum

   return @avg_price
end
GO
