网站订单转换率

sql：

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

 

 

 

 

--平台订单转化率
--对比  按网站下单时间查询  再匹配平台存在多少张单号相同的订单 统计出转化率

ALTER                       proc P_order_change2

--@web varchar(50)='0',  --网站前缀，代号
@startDate varchar(50)='0', --网站下单时间 开始
@endDate varchar(50)='0', --网站下单时间 结束
@depId varchar(50)='0',   -- 部门id
@typeId varchar(50)='0'  --网站ID

as
declare @strDate varchar(1000)
declare @strSql nvarchar(4000)
declare @strweb varchar(1000)
declare @strWeb2 nvarchar(3000)

create table #figo_order_change
(
web varchar(200)  null,
ordercode  varchar(500) null,
adddate datetime null
)

set @strDate=' and 1=1'

if @startDate!='0'
begin
    set @strDate=@strDate+' and  adddate>='''+@startDate+''''
 
end


if @endDate!='0'
begin
    set @strDate=@strDate+' and adddate<'''+@endDate+''''

end

--select ' ' as ordercode,getdate() as adddate into #figo_order_chang

if @typeId!='0'
begin

set  @strweb=@typeId
   set  @strweb=@strweb+'_orders'
  
            set @strWeb2='insert into #figo_order_change
                 select '''+ @typeId+''' as web, ordercode,adddate from '+@strweb


exec sp_executesql  @strWeb2

end

else
if @typeId='0' and  @depId='1'       ---中文              
begin
   
               insert into  #figo_order_change select * from
    (
                 select 'lv' AS web,ordercode,adddate from lv_orders --中文
    union all
             select 'gucci88' AS web ,ordercode,adddate from gucci88_orders --中文
    union all
             select 'jpgshop' AS web , ordercode,adddate from jpgshop_orders --中文
    union all
             select 'lv886'  AS web,ordercode,adddate from lv886_orders --中文
    ) as b
 end
else
 if @typeId='0' and  @depId='2'              --英文
 begin

               insert into  #figo_order_change select * from
        (select 'ilv' as web,ordercode,adddate from ilv_orders 
    union all
         select 'baghome' as web,ordercode,adddate from baghome_orders
    union all
             select 'brandfans' as web ,ordercode,adddate from brandfans_orders
    union all
             select 'brandtownjp' as web ,ordercode,adddate from brandtownjp_orders
    union all
             select 'lovingcopy' as web,ordercode,adddate from lovingcopy_orders
    union all        
             select 'lvrolex' as web,ordercode,adddate from lvrolex_orders
    union all
             select 'mybags' as web,ordercode,adddate from mybags_orders
    union all
             select 'mywatch' as web,ordercode,adddate from mywatch_orders
    union all
             select 'supakopi' as web, ordercode,adddate from supakopi_orders
    union all
             select 'vip' as web,ordercode,adddate from vip_orders
) as b

 end

else
 if @typeId='0' and  @depId='0'                ---所有
begin
            insert into  #figo_order_change select * from
        (select 'ilv'as web,ordercode,adddate from ilv_orders
    union all
             select 'lv' as web,ordercode,adddate from lv_orders
    union all
         select 'baghome' as web,ordercode,adddate from baghome_orders
    union all
             select 'brandfans' as web,ordercode,adddate from brandfans_orders
    union all
            select 'brandtownjp' as web,ordercode,adddate from brandtownjp_orders
    union all
             select 'gucci88' as web,ordercode,adddate from gucci88_orders
    union all
             select 'jpgshop' as web, ordercode,adddate from jpgshop_orders
    union all
             select 'lovingcopy' as web,ordercode,adddate from lovingcopy_orders
    union all
             select 'lv886' as web,ordercode,adddate from lv886_orders
    union all
             select 'lvrolex' as web,ordercode,adddate from lvrolex_orders
    union all
             select 'mybags' as web,ordercode,adddate from mybags_orders
    union all
             select 'mywatch' as web,ordercode,adddate from mywatch_orders
    union all
             select 'supakopi' as web,ordercode,adddate from supakopi_orders
    union all
             select 'vip' as web,ordercode,adddate from vip_orders
    ) as b

end

--select (select count(id) from ym_orders where code in(select ordercode from #figo_order_change))as ymNum,(select count(ordercode) from #figo_order_change) as webNum,round((select count(id) from ym_orders where code in(select ordercode from #figo_order_change))*100.0/(select count(ordercode) from #figo_order_change),2) as p into #figo_p
--select 0 as ymNum,0 as webNum,convert(numeric(8,2),0) as p into #figo_p
create table #figo_p
(
web varchar(200) null,
ymNum int null,
webNum int null,
p float null
)


set @strSql='select web,(select count(*) from ym_orders where status>0 and Code in (select ordercode from #figo_order_change where web=b.web '+@strDate+'))
 as  ymNum ,
(select count(*) from #figo_order_change  where  web=b.web '+@strDate+')
 as webNum ,
(case when
(select count(*) from #figo_order_change where  web=b.web '+@strDate+')=0  then 0
else
cast((select count(*) from ym_orders where status>0 and Code in (select ordercode from #figo_order_change where web=b.web '+@strDate+'))*1.0*100/(select count(*) from #figo_order_change  where  web=b.web '+@strDate+')  as numeric(10, 2))
 end
)as p
 from #figo_order_change b group by web'

 

 

exec sp_executesql @strSql

--print @strSql

--select @strweb
--select * from #figo_p

drop table #figo_p
drop table #figo_order_change

 

--select * from #figo_order_change

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO