CREATE PROCEDURE AnalysisInfo
@f_year numeric(18)=' ',
@f_month numeric(18)=' 'AS
declare @je money /*总收入*/
declare @strTmp varchar(4000)
select area as areab,count(id) as countb into #a from business where logout=1 group by area order by countb desc
/*地区-企业数*/
select areab,countb,id as bus_id into #b from #a left join business on #a.areab=business.area
/*地区-企业id*/
if @f_year <>null and @f_month <>null
/*##C全局临时表*/
set @strTmp=' select * into ##c from #b as b left join info as c on b.bus_id=c.business_id where CheckU=1 and f_year='+cast(@f_year AS varchar)+' and f_month='+cast(@f_month AS varchar)
else
set @strTmp='select * into ##c from #b as b left join info as c on b.bus_id=c.business_id where CheckU=1'
exec(@strTmp)/*地区企业-上报信息*/select areab,sum(je_lsr)as lsr into #d from ##c group by areab
drop table ##c /*手动释放全局临时表*/
/*地区-营业额总值*/
select #a.areab,#a.countb,#d.lsr into #e from #a left join #d on #a.areab=#d.areab
update #e set lsr=0 where lsr is Null
select areab,countb,lsr,cast(lsr/countb as decimal(10,4)) as pjlsr,lsr as 全市全年, lsr as 比例 into #f from #e
/*地区-平均营业额*/select @je=sum(lsr) from #f /*总收入*/
update #f set 全市全年=@je
if @je<>0
update #f set 比例=lsr*100/全市全年
else
update #f set 比例=0
select * from #f
GO
CREATE PROCEDURE InfoCompare
@f_year numeric(18)='',
@f_month numeric(18)='',
@f_year1 numeric(18)='',
@f_month1 numeric(18)=''AS
declare @je money /*总营业额*/
declare @strTmp varchar(4000) /*变量存放时间条件*/
declare @d_year varchar(40)
declare @d_month varchar(40)set @d_year=cast(@f_year as int)*100+cast(@f_month as int)
set @d_month=cast(@f_year1 as int)*100+cast(@f_month1 as int)/*set @strTmp=' where CheckU=1 and (f_year*100+f_month)>='+@d_year+' and (f_year*100+f_month)<='+@d_month*/select @je=sum(je_lsr) from v_info /*总营业额*/
where CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_month/*统计地区的企业总数，地区的营业额,占全市的比重*/
select area as area,count(distinct business_id) as countbusiness,sum(je_lsr) as lsr,sum(je_lsr)*100/@je as J_lsr into #a
from v_info
where CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_month group by area/*向上合并*/
union all
select '合计',count(distinct business_id),sum(je_lsr), 100 from v_info
where CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_monthselect * from #a
GO
CREATE PROCEDURE InfoCompare123
@f_year numeric(18)='',
@f_month numeric(18)='',
@f_year1 numeric(18)='',
@f_month1 numeric(18)=''AS
declare @strTmp varchar(4000) /*变量存放时间条件*/
declare @d_year varchar(40)
declare @d_month varchar(40)set @d_year=cast(@f_year as int)*100+cast(@f_month as int)
set @d_month=cast(@f_year1 as int)*100+cast(@f_month1 as int)set @strTmp='declare @je numeric '
set @strTmp=@strTmp+char(10)+'select @je=sum(je_lsr) from v_info where CheckU=''1'''
set @strTmp=@strTmp+char(10)+' and f_year*100+f_month>='+@d_year+' and f_year*100+f_month<='+@d_month+''
set @strTmp=@strTmp+char(10)+'print @je'
exec(@strTmp)
GO
CREATE PROCEDURE InfoCompareCY
@f_year numeric(18)='',
@f_month numeric(18)='',
@f_year1 numeric(18)='',
@f_month1 numeric(18)=''AS
declare @je money /*总营业额*/
declare @strTmp varchar(4000) /*变量存放时间条件*/
declare @d_year varchar(40)
declare @d_month varchar(40)set @d_year=cast(@f_year as int)*100+cast(@f_month as int)
set @d_month=cast(@f_year1 as int)*100+cast(@f_month1 as int)/*set @strTmp=' where CheckU=1 and (f_year*100+f_month)>='+@d_year+' and (f_year*100+f_month)<='+@d_month*/select @je=sum(je_lsr) from v_info /*总营业额*/
where hyname='餐饮' and CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_month/*统计地区的企业总数，地区的营业额,占全市的比重*/
select area as area,count(distinct business_id) as countbusiness,sum(je_lsr) as lsr,sum(je_lsr)*100/@je as J_lsr into #a
from v_info
where hyname='餐饮' and CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_month group by area/*向上合并*/
union all
select '合计',count(distinct business_id),sum(je_lsr), 100 from v_info
where hyname='餐饮' and CheckU='1' and (f_year*100+f_month)>=@d_year and f_year*100+f_month<=@d_monthselect * from #a
GO
