CREATE procedure [dbo].[mobile_status_sum_date3] @fromDate varchar(19), @toDate varchar(19)
/*
exec mobile_status_sum_date3 '2009-11-01','2009-11-29'
*/
as
begin
/*
if(ISNULL(@fromDate,'')='')
set @fromDate=convert(varchar(8),getdate()-1,121)+'01'
if(ISNULL(@toDate,'')='')
set @toDate=convert(varchar(10),getdate()-1,121)
*/
---明细表
create table #Temp_db1(
mobileNo varchar(50),
userState int,
cDate datetime,
uDate datetime );--汇总
/*
sum_active int, --激活用户
sum_unactive int, --未激活用户
sum_activing int, --活跃用户
sum_adding int --新增用户
*/insert into #Temp_db1(mobileNo, userState, cDate, uDate)
select mobile as mobileNo, 1 as userState, cdate, udate
from IMSI2MOBILE im
where (convert(varchar(10),uDate,120)>=@fromDate or ISNULL(@fromDate,'')='')
and (convert(varchar(10),uDate,120)<=@toDate or ISNULL(@toDate,'')='')
union all
select mobileNO, userState, cdate, udate
from unActiveUser un where userState=0
and (convert(varchar(10),uDate,120)>=@fromDate or ISNULL(@fromDate,'')='')
and (convert(varchar(10),uDate,120)<=@toDate or ISNULL(@toDate,'')='')select convert(varchar(10),uDate,120) as Date,
sum(case when userState=1 then 1 else 0 end) as sum_active, --
sum(case when userState=0 then 1 else 0 end) as sum_unactive,
sum(case when userState=1 and datediff(day,uDate,convert(datetime,@todate,120))<=3 then 1 else 0 end) as sum_activing,
count(1) as sum_adding
from #Temp_db1
group by convert(varchar(10),uDate,120)
order by Date;drop table #Temp_db1;end
/*
exec mobile_status_sum_date3 '2009-11-01','2009-11-29'
*/
as
begin
/*
if(ISNULL(@fromDate,'')='')
set @fromDate=convert(varchar(8),getdate()-1,121)+'01'
if(ISNULL(@toDate,'')='')
set @toDate=convert(varchar(10),getdate()-1,121)
*/
---明细表
create table #Temp_db1(
mobileNo varchar(50),
userState int,
cDate datetime,
uDate datetime );--汇总
/*
sum_active int, --激活用户
sum_unactive int, --未激活用户
sum_activing int, --活跃用户
sum_adding int --新增用户
*/insert into #Temp_db1(mobileNo, userState, cDate, uDate)
select mobile as mobileNo, 1 as userState, cdate, udate
from IMSI2MOBILE im
where (convert(varchar(10),uDate,120)>=@fromDate or ISNULL(@fromDate,'')='')
and (convert(varchar(10),uDate,120)<=@toDate or ISNULL(@toDate,'')='')
union all
select mobileNO, userState, cdate, udate
from unActiveUser un where userState=0
and (convert(varchar(10),uDate,120)>=@fromDate or ISNULL(@fromDate,'')='')
and (convert(varchar(10),uDate,120)<=@toDate or ISNULL(@toDate,'')='')select convert(varchar(10),uDate,120) as Date,
sum(case when userState=1 then 1 else 0 end) as sum_active, --
sum(case when userState=0 then 1 else 0 end) as sum_unactive,
sum(case when userState=1 and datediff(day,uDate,convert(datetime,@todate,120))<=3 then 1 else 0 end) as sum_activing,
count(1) as sum_adding
from #Temp_db1
group by convert(varchar(10),uDate,120)
order by Date;drop table #Temp_db1;end
as
type myrctype is ref cursor;
procedure mobile_status_sum_date(v_fromDate VARCHAR2,v_toDate VARCHAR2, p_rc out myrctype);
end pkg_mobile_status_sum_date;
/---------------------------执行存储过程---------------------------------------
create or replace package body pkg_mobile_status_sum_date
as
procedure mobile_status_sum_date(v_fromDate VARCHAR2,v_toDate VARCHAR2, p_rc out myrctype)
is
sqlstr VARCHAR2(1000);
begin insert into mobile_ssd_proc_tmp(mobileNo, userState, cDate, uDate)
select mobile as mobileNo, 1 as userState, cdate, udate
from IMSI2MOBILE im
where uDate>=to_date(NVL(v_fromDate,sysdate-1),'YYYY-MM-DD')
and uDate<=to_date(NVL(v_toDate,sysdate),'YYYY-MM-DD')
union all
select mobileNO, userState, cdate, udate
from unActiveUser un where userState=0
and uDate>=to_date(NVL(v_fromDate,sysdate-1),'YYYY-MM-DD')
and uDate<=to_date(NVL(v_toDate,sysdate),'YYYY-MM-DD'); sqlstr :=
'select to_char(uDate,''YYYY-MM-DD'') as uDate,
sum(case when userState=1 then 1 else 0 end) as sum_active,
sum(case when userState=0 then 1 else 0 end) as sum_unactive,
sum(case when userState=1 and uDate+3>=to_date(:v_toDate,''YYYY-MM-DD'') then 1 else 0 end) as sum_activing,
count(1) as sum_adding
from mobile_ssd_proc_tmp where uDate<=to_date(:v_toDate,''YYYY-MM-DD'')
group by to_char(uDate,''YYYY-MM-DD'') ';
OPEN p_rc FOR sqlstr USING v_toDate, v_toDate; end mobile_status_sum_date;
end pkg_mobile_status_sum_date;
/
------------------------------------------------------------------------------------
set serveroutput on;
var c_cur refcursor
exec pkg_mobile_status_sum_date.mobile_status_sum_date('2009-01-01','2009-12-01',:c_cur);
print c_cur;