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



CREATE function fn_get_ret_frenum (@date_begin datetime,@date_end datetime,@branch_no varchar(6))
returns decimal(16,2)
begin
	declare   @tal_gmnum		numeric(16,2),
			  @vip_num			numeric(16,2),
			  @avg_num			numeric(16,2)
		
	select @tal_gmnum = count(distinct s.flow_no),@vip_num = count(distinct p.card_no) from t_rm_saleflow s with(nolock) left join t_rm_payflow p on s.flow_no = p.flow_no
	where isnull(p.card_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(@vip_num,0) = 0
		set @avg_num = 0
	else
		set @avg_num = @tal_gmnum / @vip_num

   return @avg_num
end
GO
