- effective member last month effective this month than member guest list last month, this month compared to the guest list
- this month compared to store frequency last month to store this month than member sales of last month's sales accounted for
- member consumption last month this month consumption compared to
- note for last month's total sales last month new members
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
The ALTER proc [dbo] [HFYK_bbfx_hyjx]
@ KSRQ char (10)=', - start date
@ JSRQ char (10)=",
- deadline@ xl char (2)=",
@ duifbsh varchar (11)='%', - branch id
@ entid varchar (11)='
As
- the exec hfyk_bbfx_hyjx '2016-04-01', '2016-04-30', 'A', ' 'and' E1N3SZFNIB6 '
- select * from entdoc
- declare @ KSRQ char (10)
- declare @ JSRQ char (10)
- member performance this month
IF (@ DUIFBSH='OR the EXISTS (SELECT 1 FROM ORGDOC where ISORG=' Y 'AND ORGID=@ DUIFBSH))
The BEGIN
The SET @ duifbsh='%'
END
The 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
)
- member performance last month
The 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'
The begin
Insert into # byhyjx (FDBS, byhylks byyxhy, byhykdj, byhyxszb, byhyxshe, byxshe, byxzhy, byprofit, byprofitrate)
Select ID as a.ORG duifbsh, b.b yhylks, b.b yyxhy, b.b yhykdj, b.b yhyxshe/e.b yxshe as byhyxszb, b.b yhyxshe, e.b yxshe,
Isnull (db yxzhy, 0) as byxzhy, p. rofit, p. rofitrate
The from ORGDOC a
Left the join (select entid, OrgId as FDBS, count (cardid) byhylks, isnull (count (distinct cardid), 0) as byyxhy, SUM (PaidInAmt) as byhyxshe,
The SUM (profit) as profit, round (SUM (profit)/SUM (PaidInAmt), 2) the as profitrate,
The case when the count (cardid)=0 then 0 else sum (PaidInAmt)/count (cardid) end byhykdj
The from retbillmt where cardid<> "' AND Dates>=@ KSRQ and Dates<=@ JSRQ
Group by entid, OrgId) b on a.ORG ID=b. DBS and a.E NTID=b.E NTID
Left the join (select entid, OrgId as FDBS, SUM (PaidInAmt) as byxshe
The from retbillmt WHERE Dates>=@ KSRQ and Dates<=@ JSRQ
Group by entid, OrgId) e on a.ORG ID=e.f DBS and a.E NTID=e.E NTID
Left the join (select entid fkorgid, isnull (count (cardid), 0) as byxzhy from CARDDOC
Where fakrq>=@ KSRQ and fakrq
- last month
Insert into # syhyjx (FDBS, syhylks syyxhy, syhykdj, syhyxszb, syhyxshe, syxshe, syxzhy, syprofit, syprofitrate)
Select ID as a.ORG duifbsh, b.b yhylks, b.b yyxhy, b.b yhykdj, b.b yhyxshe/e.b yxshe as byhyxszb, b.b yhyxshe, e.b yxshe,
Isnull (db yxzhy, 0) as byxzhy, p. rofit, p. rofitrate
The from ORGDOC a
Left the join (select entid, OrgId as FDBS, count (cardid) byhylks, isnull (count (distinct cardid), 0) as byyxhy, SUM (PaidInAmt) as byhyxshe,
The SUM (profit) as profit, round (SUM (profit)/SUM (PaidInAmt), 2) the as profitrate,
The case when the count (cardid)=0 then 0 else sum (PaidInAmt)/count (cardid) end byhykdj
The from retbillmt where cardid<> "' AND Dates>=the convert (char (10), dateadd (mm, 1, @ KSRQ), 121) and Dates<=the convert (char (10), dateadd (mm, 1, @ JSRQ), 121)
Group by entid, OrgId) b on a.ORG ID=b. DBS and a.E NTID=b.E NTID
Left the join (select entid, OrgId as FDBS, SUM (PaidInAmt) as byxshe, COUNT (billno) as bylks
The from retbillmt WHERE Dates>=the convert (char (10), dateadd (mm, 1, @ KSRQ), 121) and Dates<=the convert (char (10), dateadd (mm, 1, @ JSRQ), 121)
Group by entid, OrgId) e on a.ORG ID=e.f DBS and a.E NTID=e.E NTID
Left the join (select entid fkorgid, isnull (count (cardid), 0) as byxzhy from CARDDOC
Where fakrq>=the convert (char (10), dateadd (mm, 1, @ KSRQ), 121) and fakrq<=the convert (char (10), dateadd (mm, 1, @ JSRQ), 121) group by entid, fkorgid) d on a.E NTID=d.E NTID AND a.ORG ID=d.f korgid
Where ID like @ a.ORG duifbsh
No, select a.org a.org code, a.org NAME,
New member | byxzhy as [this month], syxzhy as [| new members last month], byxzhy - isnull (syxzhy, 0) as [| new members compare],
Active member byyxhy as [| this month], syyxhy as effective member | [] last month, byyxhy - isnull (syyxhy, 0) as active member [|],
Byhylks as visitor Numbers | [members] this month, syhylks as visitor Numbers | [members] last month, byhylks - isnull (syhylks, 0) as visitor Numbers | [members],
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull