-会员绩效分析：机构名称 本月会员来客数 上月会员来客数 相比
--本月有效会员 上月有效会员 相比 本月会员客单 上月会员客单 相比
--本月来店频率 上月来店频率 相比 本月会员销售占比 上月会员销售占比 相比
--本月会员消费额 上月会员消费额 相比
--备注片区 上月总销售 上月新增会员
ALTER proc [dbo].[HFYK_bbfx_hyjx]
@ksrq char(10)='', --开始日期
@jsrq char(10)='', --截止日期
@xl char(2)='',
@duifbsh varchar(11) = '%', --分店标识
@entid varchar(11)=''
as
---exec hfyk_bbfx_hyjx '2016-04-01','2016-04-30','A','','E1N3SZFNIB6'
--select * from entdoc
--declare @ksrq char(10)
--declare @jsrq char(10)
--本月会员绩效
IF (@DUIFBSH = '' OR EXISTS(SELECT 1 FROM ORGDOC where ISORG='Y' AND ORGID = @duifbsh))
BEGIN
SET @duifbsh = '%'
END
create table #byhyjx
(
fdbs varchar(20) null default '',
byhylks decimal(14,2) null default 0,
byyxhy decimal(14,2) null default 0,
byhykdj decimal(14,2) null default 0,
byhyldpl decimal(14,2) null default 0,
byhyxszb decimal(14,2) null default 0,
byhyxshe decimal(14,2) null default 0,
byxshe decimal(14,2) null default 0,
byxzhy decimal(14,2) null default 0,
byprofit decimal(14,2) null default 0,
byprofitrate decimal(14,2) null default 0
)
--上月会员绩效
create table #syhyjx
(
fdbs varchar(20) null default '',
syhylks decimal(14,2) null default 0,
syyxhy decimal(14,2) null default 0,
syhykdj decimal(14,2) null default 0,
syhyldpl decimal(14,2) null default 0,
syhyxszb decimal(14,2) null default 0,
syhyxshe decimal(14,2) null default 0,
syxshe decimal(14,2) null default 0,
syxzhy decimal(14,2) null default 0,
syprofit decimal(14,2) null default 0,
syprofitrate decimal(14,2) null default 0
)
--select @ksrq='2012-07-01',@jsrq='2012-07-19'
if @xl='A'
begin
insert into #byhyjx(fdbs,byhylks,byyxhy,byhykdj,byhyxszb,byhyxshe,byxshe,byxzhy,byprofit,byprofitrate)
select a.ORGID as duifbsh,b.byhylks,b.byyxhy,b.byhykdj,b.byhyxshe/e.byxshe AS byhyxszb,b.byhyxshe,e.byxshe,
isnull(d.byxzhy,0) as byxzhy,b.profit,b.profitrate
from ORGDOC a
left join (select entid,OrgId as fdbs,count(cardid) byhylks,isnull(count(distinct cardid),0) as byyxhy,SUM(PaidInAmt) AS byhyxshe,
SUM(profit) as profit,round(SUM(profit)/SUM(PaidInAmt),2) AS profitrate,
case when count(cardid)=0 then 0 else sum(PaidInAmt)/count(cardid) end byhykdj
from retbillmt where cardid<>'' AND Dates>=@ksrq and Dates<=@jsrq
group by entid,OrgId) b on a.ORGID=b.fdbs and a.ENTID=b.EntId
left join (select entid,OrgId as fdbs,SUM(PaidInAmt) AS byxshe
from retbillmt WHERE Dates>=@ksrq and Dates<=@jsrq
group by entid,OrgId) e on a.ORGID=e.fdbs and a.ENTID=e.EntId
left join (select entid,fkorgid,isnull(count(cardid),0) as byxzhy from CARDDOC
where fakrq>=@ksrq and fakrq<=@jsrq group by entid,fkorgid) d on a.ENTID=d.EntId AND a.ORGID=d.fkorgid
where a.ORGID like @duifbsh
--上月
insert into #syhyjx(fdbs,syhylks,syyxhy,syhykdj,syhyxszb,syhyxshe,syxshe,syxzhy,syprofit,syprofitrate)
select a.ORGID as duifbsh,b.byhylks,b.byyxhy,b.byhykdj,b.byhyxshe/e.byxshe AS byhyxszb,b.byhyxshe,e.byxshe,
isnull(d.byxzhy,0) as byxzhy,b.profit,b.profitrate
from ORGDOC a
left join (select entid,OrgId as fdbs,count(cardid) byhylks,isnull(count(distinct cardid),0) as byyxhy,SUM(PaidInAmt) AS byhyxshe,
SUM(profit) as profit,round(SUM(profit)/SUM(PaidInAmt),2) AS profitrate,
case when count(cardid)=0 then 0 else sum(PaidInAmt)/count(cardid) end byhykdj
from retbillmt where cardid<>'' AND Dates>=convert(char(10),dateadd(mm,-1,@ksrq),121)and Dates<=convert(char(10),dateadd(mm,-1,@jsrq),121)
group by entid,OrgId) b on a.ORGID=b.fdbs and a.ENTID=b.EntId
left join (select entid,OrgId as fdbs,SUM(PaidInAmt) AS byxshe,COUNT(billno) AS bylks
from retbillmt WHERE Dates>=convert(char(10),dateadd(mm,-1,@ksrq),121) and Dates<=convert(char(10),dateadd(mm,-1,@jsrq),121)
group by entid,OrgId) e on a.ORGID=e.fdbs and a.ENTID=e.EntId
left join (select entid,fkorgid,isnull(count(cardid),0) as byxzhy from CARDDOC
where fakrq>=convert(char(10),dateadd(mm,-1,@ksrq),121) and fakrq<=convert(char(10),dateadd(mm,-1,@jsrq),121) group by entid,fkorgid) d on a.ENTID=d.EntId AND a.ORGID=d.fkorgid
where a.ORGID like @duifbsh
select a.orgno,a.orgcode,a.ORGNAME ,
byxzhy as [新增会员|本月],syxzhy as [新增会员|上月],byxzhy-isnull(syxzhy,0) as [新增会员|比较],
byyxhy as [有效会员|本月],syyxhy as [有效会员|上月],byyxhy -isnull(syyxhy,0) as [有效会员|比较],
byhylks as [会员来客数|本月],syhylks as [会员来客数|上月],byhylks -isnull(syhylks,0) as [会员来客数|比较],
byhykdj as [会员客单价|本月],syhykdj as [会员客单价|上月],byhykdj-isnull(syhykdj,0) as [会员客单价|比较],
byxshe as [销售总额|本月], syxshe as [销售总额|上月],byxshe-isnull(syxshe,0) as [销售总额|比较],
byhyxshe as [会员销售额|本月],syhyxshe as [会员销售额|上月],byhyxshe-isnull(syhyxshe,0) as [会员销售额|比较],
byhyxszb as [会员销售占比|本月],syhyxszb as [会员销售占比|上月],byhyxszb-isnull(syhyxszb,0)as [会员销售占比|比较],
byprofit as [会员毛利|本月],syprofit as [会员毛利|上月],byprofit-isnull(syprofit,0) as [会员毛利|比较],
byprofitrate as [会员毛利率|本月],syprofitrate as [会员毛利率|上月],a.entid
from ORGDOC a ,#byhyjx b,#syhyjx c
where a.ORGID=b.fdbs and a.ORGID=c.fdbs AND a.ENTID=@entid AND a.IsOrg<>'Y'
order by a.ORGID
drop table #byhyjx
drop table #syhyjx
end
