DROP PROCEDURE IF EXISTS `Sum_Month_Cus_Count`;
CREATE PROCEDURE `Sum_Month_Cus_Count`()
BEGIN
declare v_cur_Date date;
declare v_pre1_Date date;
declare v_year,v_month,v_count int;
set v_cur_Date = CURDATE();
set v_pre1_Date = date_add(v_cur_Date, interval -1 day) ;
set v_year =year(v_pre1_Date);
set v_month = month(v_pre1_Date);
select count(syear) into v_count from rpl_month_cycle where syear =v_year and smonth=v_month;
if v_count=0 THEN
INSERT into rpl_month_cycle(syear,smonth) values(v_year,v_month);
end if;
delete from rpl_cus_month_sum where syear =v_year and smonth=v_month;
select v_year,v_month ,ta.groupId,IFNULL(tb.addNum,0),0
from cus_group as ta
left join
(
select egroupId,count(egroupId) as addNum from cus_costomer_up_history where year(changeDate) = v_year and month(changeDate)=v_month group by egroupId
) as tb
on ta.groupId = tb.egroupId
where ta.IsSum =1;
insert into rpl_cus_month_sum
select v_year,v_month ,ta.groupId,IFNULL(tb.addNum,0),0
from cus_group as ta
left join
(
select egroupId,count(egroupId) as addNum from cus_costomer_up_history where year(changeDate) = v_year and month(changeDate)=v_month group by egroupId
) as tb
on ta.groupId = tb.egroupId
where ta.IsSum =1;
update rpl_cus_month_sum as ta,
(
select groupId,count(groupId) as totalNum from cus_customer where State=1 group by groupId
) as tb
set ta.totalNum = tb.totalNum
where ta.groupId = tb.groupId
and ta.syear = v_year
and ta.smonth=v_month;
end |
|
转播
