set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go-----功能:按门店统计进店率,成交率
-----Create by Cohen Date:20101118
-----执行方法:exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100'
ALTER 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)
----查询对应门店人员数量
if @PersonNum is not null
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;怎样让这个存储过程按照下面两种方式都能执行:
exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100','6'
go
exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100'
set QUOTED_IDENTIFIER ON
go-----功能:按门店统计进店率,成交率
-----Create by Cohen Date:20101118
-----执行方法:exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100'
ALTER 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)
----查询对应门店人员数量
if @PersonNum is not null
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;怎样让这个存储过程按照下面两种方式都能执行:
exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100','6'
go
exec SaleOut_EnterRateByStore_test '20101101','20101130','60400101','30','100'
(
@startdate datetime, --开始统计时间
@enddate datetime, --结束统计时间
@ORGEH nvarchar(50), --接待门店编号
@countdays int, --常理按30天/每月计算
@neednum int, --统计标准常规按每店/月接待100人.
@PersonNum int=6 ----查询对应门店人员数量
GO
CREATE PROCEDURE PROC_MU(@I1 INT=0,@I2 INT=0)
AS
SELECT @I1+@I2
GO
EXEC PROC_MU
--0
EXEC PROC_MU 1
--1
EXEC PROC_MU 1,2
--3