create or replace procedure DXGJ_GQY_CZXF
(开始日期 in date,
结束日期 in date,
val out sys_refcursor)
is
s_StartDate varchar2(20):= sysdate;
s_EndDate varchar2(20):= sysdate;
s_StartDateYear varchar2(20):='';
-- S_EndDateYear varchar2(20):='';
begin
execute immediate 'create table czxf (组织名称 varchar(50),日储值 varchar(20),日占比 Decimal(10,2),月储值 varchar(20),月占比 Decimal(10,2),年储值 varchar(20),年占比 Decimal(10,2),日消费 varchar(20),日消费占比 Decimal(10,2), 月消费 varchar(20),月消费占比 Decimal(10,2),年消费 varchar(20),年消费占比 Decimal(10,2))';
s_StartDate:= to_char(开始日期,'yyyy-mm-dd');
s_EndDate:= to_char (结束日期,'yyyy-mm-dd');
s_StartDateYear:=to_char(开始日期,'yyyy');
open val for
insert into czxf (组织名称,日储值,月储值,年储值)
(select t_org_sale.fname_l2 组织名称,chuzhiri 日储值,chuzhiyue 月储值,chuzhinian 年储值 from
(select sum (FStoreMoney) chuzhinian,Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where
to_char(t_cyh_cardstore.fbizdate,'yyyy')>=s_StartDateYear and
to_char(T_CYH_CardStore.fbizdate,'yyyy-mm-dd')<=s_EndDate
group by Fsaleorgunitid)nianchuzhi--年储值
left join
(select sum (FStoreMoney) chuzhiri, Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where --to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') >= s_StartDate
to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') = s_EndDate
group by Fsaleorgunitid) richuzhi--日储值
on nianchuzhi.Fsaleorgunitid = richuzhi.Fsaleorgunitid
left join
(select sum(FStoreMoney) chuzhiyue,Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') >= s_StartDate
and to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') <= s_EndDate
group by Fsaleorgunitid) yuechuzhi--月储值
on nianchuzhi.Fsaleorgunitid = yuechuzhi.Fsaleorgunitid
left join t_org_sale on nianchuzhi.Fsaleorgunitid = t_org_sale.fid );
end DXGJ_GQY_CZXF;
(开始日期 in date,
结束日期 in date,
val out sys_refcursor)
is
s_StartDate varchar2(20):= sysdate;
s_EndDate varchar2(20):= sysdate;
s_StartDateYear varchar2(20):='';
-- S_EndDateYear varchar2(20):='';
begin
execute immediate 'create table czxf (组织名称 varchar(50),日储值 varchar(20),日占比 Decimal(10,2),月储值 varchar(20),月占比 Decimal(10,2),年储值 varchar(20),年占比 Decimal(10,2),日消费 varchar(20),日消费占比 Decimal(10,2), 月消费 varchar(20),月消费占比 Decimal(10,2),年消费 varchar(20),年消费占比 Decimal(10,2))';
s_StartDate:= to_char(开始日期,'yyyy-mm-dd');
s_EndDate:= to_char (结束日期,'yyyy-mm-dd');
s_StartDateYear:=to_char(开始日期,'yyyy');
open val for
insert into czxf (组织名称,日储值,月储值,年储值)
(select t_org_sale.fname_l2 组织名称,chuzhiri 日储值,chuzhiyue 月储值,chuzhinian 年储值 from
(select sum (FStoreMoney) chuzhinian,Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where
to_char(t_cyh_cardstore.fbizdate,'yyyy')>=s_StartDateYear and
to_char(T_CYH_CardStore.fbizdate,'yyyy-mm-dd')<=s_EndDate
group by Fsaleorgunitid)nianchuzhi--年储值
left join
(select sum (FStoreMoney) chuzhiri, Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where --to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') >= s_StartDate
to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') = s_EndDate
group by Fsaleorgunitid) richuzhi--日储值
on nianchuzhi.Fsaleorgunitid = richuzhi.Fsaleorgunitid
left join
(select sum(FStoreMoney) chuzhiyue,Fsaleorgunitid from T_CYH_CardStoreEntry
left join T_CYH_CardStore on T_CYH_CardStoreEntry.FParenTID=T_CYH_CardStore.Fid
where to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') >= s_StartDate
and to_char(T_CYH_CardStore.FCreateTime,'yyyy-mm-dd') <= s_EndDate
group by Fsaleorgunitid) yuechuzhi--月储值
on nianchuzhi.Fsaleorgunitid = yuechuzhi.Fsaleorgunitid
left join t_org_sale on nianchuzhi.Fsaleorgunitid = t_org_sale.fid );
end DXGJ_GQY_CZXF;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货