@endtime datetime



 AS



   declare @tempTable  table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50))



  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)

  select b.businessid,b.business_name,sub.subbusinessid,sub.business_subname

  from business_typemain b,business_type sub

  where b.businessid=sub.businessid



  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)

  select businessid,business_name,0,' '

  from business_typemain 

  where businessid not in (select businessid from business_type)



  declare business cursor for

        select businessid,subBusinessid,businessname,business_subname from @tempTable

   open business



   declare @tempNumber  table(business_name varchar(50),business_subname varchar(50),n int)



   while 1 = 1

    begin

        declare @bid int

        declare @subid int

        declare @bname varchar(50)

        declare @subname varchar(50)



        fetch next from business into @bid,@subid,@bname,@subname

        if @@fetch_status <> 0

            break



       insert into @tempNumber(business_name,business_subname,n)

       values(@bname,@subname,0)



       insert into @tempNumber(business_name,business_subname,n)

       select @bname,@subname,1

       from archive

       where jointime <=@endtime and jointime>=@starttime and CAST(SUBSTRING(registerid, 1, 2) AS int)=@bid and CAST(SUBSTRING(registerid, 3, 2) AS int)=@subid



    end

  

 close business

 deallocate business



 select business_name,sum(n) as num

 from @tempNumber

 group by  business_name













GO

create function f_tempNumber

(@starttime datetime,

@endtime datetime

)

returns @tempNumber  table(business_name varchar(50),business_subname varchar(50),n int)

begin

declare @tempTable  table(businessId int,businessname varchar(50),subbusinessid int,business_subname varchar(50))



  insert into @tempTable (businessid,businessname,subbusinessid,business_subname)

  select b.businessid,b.business_name,sub.subbusi