-会员绩效分析:机构名称 本月会员来客数 上月会员来客数 相比
--本月有效会员 上月有效会员 相比 本月会员客单 上月会员客单 相比
--本月来店频率 上月来店频率 相比 本月会员销售占比 上月会员销售占比 相比
--本月会员消费额 上月会员消费额 相比
--备注片区 上月总销售 上月新增会员
---------------------------------------------------------------------------------------------------
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 = '%'
ENDcreate 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
--本月有效会员 上月有效会员 相比 本月会员客单 上月会员客单 相比
--本月来店频率 上月来店频率 相比 本月会员销售占比 上月会员销售占比 相比
--本月会员消费额 上月会员消费额 相比
--备注片区 上月总销售 上月新增会员
---------------------------------------------------------------------------------------------------
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 = '%'
ENDcreate 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
ORACLE是没有办法这样直接实现,在ORACLE内,需要提前创建好结构表,然后做INSERT 到结构表,作为数据集。
--下面是在当前USER下创建的表和存储过程;CREATE TABLE BYHYJX /*本月会员绩效*/
(fdbs VARCHAR(20) null default '',
byhylks NUMBER(14,2) null default 0,
byyxhy NUMBER(14,2) null default 0,
byhykdj NUMBER(14,2) null default 0,
byhyldpl NUMBER(14,2) null default 0,
byhyxszb NUMBER(14,2) null default 0,
byhyxshe NUMBER(14,2) null default 0,
byxshe NUMBER(14,2) null default 0,
byxzhy NUMBER(14,2) null default 0,
byprofit NUMBER(14,2) null default 0,
byprofitrate NUMBER(14,2) null default 0);CREATE TABLE SYHYJX /*上月会员绩效*/
(fdbs VARCHAR(20) null default '',
syhylks NUMBER(14,2) null default 0,
syyxhy NUMBER(14,2) null default 0,
syhykdj NUMBER(14,2) null default 0,
syhyldpl NUMBER(14,2) null default 0,
syhyxszb NUMBER(14,2) null default 0,
syhyxshe NUMBER(14,2) null default 0,
syxshe NUMBER(14,2) null default 0,
syxzhy NUMBER(14,2) null default 0,
syprofit NUMBER(14,2) null default 0,
syprofitrate NUMBER(14,2) null default 0);CREATE TABLE JIEGUO /*结果表*/
(....../*根据你的结果情况创建数据表*/...... )/*下面是存储过程*/
CREATE OR REPLACE HFYK_bbfx_hyjx(V_KSRQ IN CHAR(10) ,
V_JSRQ IN CHAR(10) ,
V_RETURN OUT INTEGER)
--会员绩效分析:机构名称 本月会员来客数 上月会员来客数 相比
--本月有效会员 上月有效会员 相比 本月会员客单 上月会员客单 相比
--本月来店频率 上月来店频率 相比 本月会员销售占比 上月会员销售占比 相比
--本月会员消费额 上月会员消费额 相比
--备注片区 上月总销售 上月新增会员
--V_KSRQ --开始日期
--V_JSRQ --截止日期
IS
V_DUIFBSH VARCHAR(11) := '%@%'; --分店标识 @
V_ENTID VARCHAR(11) := '';
---exec hfyk_bbfx_hyjx '2016-04-01','2016-04-30','A','','E1N3SZFNIB6'
--select * from entdoc
--declare @ksrq char(10)
--declare @jsrq char(10)
--本月会员绩效BEGIN
/*IF (@DUIFBSH = '' OR EXISTS(SELECT 1 FROM ORGDOC where ISORG='Y' AND ORGID = @duifbsh))
BEGIN
SET @duifbsh = '%'
END
if @xl='A'
begin*/
EXECUTE IMMEDIATE 'TRUNCATE TABLE BYHYJX' ;
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,
NVL(D.BYXZHY, 0) AS BYXZHY,
B.PROFIT,
B.PROFITRATE
FROM ORGDOC A
LEFT JOIN (SELECT ENTID,
ORGID AS FDBS,
COUNT(CARDID) BYHYLKS,
NVL(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 >= V_KSRQ
AND DATES <= V_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 >= V_KSRQ
AND DATES <= V_JSRQ
GROUP BY ENTID, ORGID) E
ON A.ORGID = E.FDBS
AND A.ENTID = E.ENTID
LEFT JOIN (SELECT ENTID, FKORGID, NVL(COUNT(CARDID), 0) AS BYXZHY
FROM CARDDOC
WHERE FAKRQ >= V_KSRQ
AND FAKRQ <= V_JSRQ
GROUP BY ENTID, FKORGID) D
ON A.ENTID = D.ENTID
AND A.ORGID = D.FKORGID
WHERE A.ORGID LIKE V_DUIFBSH ;
COMMIT;
--上月
EXECUTE IMMEDIATE 'TRUNCATE TABLE SYHYJX' ;
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,
NVL(D.BYXZHY, 0) AS BYXZHY,
B.PROFIT,
B.PROFITRATE
FROM ORGDOC A
LEFT JOIN (SELECT ENTID,
ORGID AS FDBS,
COUNT(CARDID) BYHYLKS,
NVL(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 >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
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 >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
GROUP BY ENTID, ORGID) E
ON A.ORGID = E.FDBS
AND A.ENTID = E.ENTID
LEFT JOIN (SELECT ENTID, FKORGID, NVL(COUNT(CARDID), 0) AS BYXZHY
FROM CARDDOC
WHERE DATES >= ADD_MONTHS(TO_DATE(V_KSRQ,'YYYYMMDD'),-1)
AND DATES <= ADD_MONTHS(TO_DATE(V_JSRQ,'YYYYMMDD'),-1)
GROUP BY ENTID, FKORGID) D
ON A.ENTID = D.ENTID
AND A.ORGID = D.FKORGID
WHERE A.ORGID LIKE V_DUIFBSH;
COMMIT;INSERT INTO JIEGUO
SELECT A.ORGNO,
A.ORGCODE,
A.ORGNAME,
BYXZHY,
SYXZHY,
BYXZHY - NVL(SYXZHY, 0) OP1,
BYYXHY,
SYYXHY,
BYYXHY - NVL(SYYXHY, 0) OP2,
BYHYLKS,
SYHYLKS,
BYHYLKS - NVL(SYHYLKS, 0) OP3,
BYHYKDJ,
SYHYKDJ,
BYHYKDJ - NVL(SYHYKDJ, 0) OP4,
BYXSHE,
SYXSHE,
BYXSHE - NVL(SYXSHE, 0) OP5,
BYHYXSHE,
SYHYXSHE,
BYHYXSHE - NVL(SYHYXSHE, 0) OP6,
BYHYXSZB,
SYHYXSZB,
BYHYXSZB - NVL(SYHYXSZB, 0) OP7,
BYPROFIT,
SYPROFIT,
BYPROFIT - NVL(SYPROFIT, 0) OP8,
BYPROFITRATE,
SYPROFITRATE,
A.ENTID
FROM ORGDOC A, BYHYJX B, SYHYJX C
WHERE A.ORGID = B.FDBS
AND A.ORGID = C.FDBS
AND A.ENTID = V_ENTID
AND A.ISORG <> 'Y'
ORDER BY A.ORGID;
COMMIT;
V_RETURN :=0;
END;
--到此结束了,两个输入变量V_KSRQ,V_JSRQ; 1个输出变量V_RETURN;
--中间的参数V_DUIFBSH和V_ENTID 就直接赋值你需要的值,数据保存在结果表;然后你操作结果表就好;