要求:
1. 当参数@PersonNum为空就查询对应门店人数:select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
2. 当参数@PersonNum不为空时就不作查询.Create procedure [dbo].[SaleOut_EnterRateByStore_test]
(
@startdate datetime, --开始统计时间
@enddate datetime, --结束统计时间
@ORGEH nvarchar(50), --接待门店编号
@countdays int, --常理按30天/每月计算
@neednum int --统计标准常规按每店/月接待100人.
------@PersonNum int --查询对应门店人员数量
)
as
begin
declare @jdsl decimal(14,4),@cjsl decimal(14,4),@PersonNum int
----查询对应门店人员数量
select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
----进店数量
select @jdsl = (select count(*) from salesinfo where substring(saleskey,0,9) between @startdate and @enddate and pernr in (select pernr from ZTA_HRDNET where ORGEH = @ORGEH))
----成交数量
select @cjsl = (select count(*) from salesinfo where feelstate = '购买' and substring(saleskey,0,9) between @startdate and @enddate and pernr in (select pernr from ZTA_HRDNET where ORGEH = @ORGEH))
---将记录写入分析表
insert into AnalysisRecords
select
distinct @ORGEH 门店编号,
门店名称 = (select distinct ORGTX from stores where ORGEH = @ORGEH),
@startdate 开始时间,
@enddate 结束时间,
@countdays 统计天数,
@jdsl,
@cjsl,
case when @jdsl=0 or datediff(day, @startdate, @enddate)=0 then 0 else @cjsl/@jdsl*30/datediff(day, @startdate, @enddate)*100 end as 成交率,
case when @neednum*@PersonNum=0 or datediff(day, @startdate, @enddate)=0 then 0 else @jdsl/(@neednum*@PersonNum)*@countdays/(datediff(day, @startdate, @enddate))*100 end as 进店率,
@neednum*@PersonNum,
Getdate()
end;
1. 当参数@PersonNum为空就查询对应门店人数:select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
2. 当参数@PersonNum不为空时就不作查询.Create procedure [dbo].[SaleOut_EnterRateByStore_test]
(
@startdate datetime, --开始统计时间
@enddate datetime, --结束统计时间
@ORGEH nvarchar(50), --接待门店编号
@countdays int, --常理按30天/每月计算
@neednum int --统计标准常规按每店/月接待100人.
------@PersonNum int --查询对应门店人员数量
)
as
begin
declare @jdsl decimal(14,4),@cjsl decimal(14,4),@PersonNum int
----查询对应门店人员数量
select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
----进店数量
select @jdsl = (select count(*) from salesinfo where substring(saleskey,0,9) between @startdate and @enddate and pernr in (select pernr from ZTA_HRDNET where ORGEH = @ORGEH))
----成交数量
select @cjsl = (select count(*) from salesinfo where feelstate = '购买' and substring(saleskey,0,9) between @startdate and @enddate and pernr in (select pernr from ZTA_HRDNET where ORGEH = @ORGEH))
---将记录写入分析表
insert into AnalysisRecords
select
distinct @ORGEH 门店编号,
门店名称 = (select distinct ORGTX from stores where ORGEH = @ORGEH),
@startdate 开始时间,
@enddate 结束时间,
@countdays 统计天数,
@jdsl,
@cjsl,
case when @jdsl=0 or datediff(day, @startdate, @enddate)=0 then 0 else @cjsl/@jdsl*30/datediff(day, @startdate, @enddate)*100 end as 成交率,
case when @neednum*@PersonNum=0 or datediff(day, @startdate, @enddate)=0 then 0 else @jdsl/(@neednum*@PersonNum)*@countdays/(datediff(day, @startdate, @enddate))*100 end as 进店率,
@neednum*@PersonNum,
Getdate()
end;
@enddate datetime= '1901-01-01 00:00:00', --结束统计时间
@ORGEH nvarchar(50)='0', --接待门店编号
@countdays int=0, --常理按30天/每月计算
@neednum int=0 --统计标准常规按每店/月接待100人.
------@PersonNum int --查询对应门店人员数量
1. 当参数@PersonNum为空就查询对应门店人数:select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
2. 当参数@PersonNum不为空时就不作查询.------
没看懂具体意思,如果是这样,可以用判断语句.
if @PersonNum is null
select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
else
其他需求,如果无其他需求可以不要else分句.
当用户不输入参数(@PersonNum int --查询对应门店人员数量)时,就去执行查询:
select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
当用户输入参数时,就不执行查询.
select @PersonNum = (select Count(*) as PersonNum from ZTA_HRDNET where ORGEH = @ORGEH and STAT2 = '3')
else
其他需求,如果无其他需求可以不要else分句.
差不多是这意思。