proc 储过程

SET QUOTED_IDENTIFIER ON  GO SET ANSI_NULLS ON  GO –**分类报表存储过程 ALTER  proc gnfl_proc  as  declare @p_phone varchar(30), @p_sex int, @p_age int, @p_city varchar(50), @p_r_type int, @p_r_time datetime,    @v_ping     int, –PING码上行总计      @v_ping_ejj int, –PING码上行其中EJJ用户数    @v_ping_bjj int, –PING码上行其中BJJ用户数    @v_ping_con int, –PING码上行中注册和未注册的用户(consumer)不含发送EJJ/BJJ用户    @v_ping_reg     int, –发送PING码并注册的用户（含不在同一天注册的用户）    @v_ping_reg_ejj int, –发送PING码并注册且发送过EJJ的用户    @v_ping_reg_bjj int, –发送PING码并注册且发关过BJJ的用户    @v_ping_reg_con int, –发送PING码注册的用户(consumer)不含发送过EJJ/BJJ的用户    @v_ping_noreg     int, –发送PING码未注册的用户    @v_ping_noreg_ejj int, –发送PING码未注册而且发送了EJJ的用户    @v_ping_noreg_bjj int, –发送PING码未注册而且发送了BJJ的用户    @v_ping_noreg_con int, –发送PING码未注册也示发送EJJ/BJJ的用户(consumer)]    @v_man    int, –男性    @v_women  int, –女性    @v_age20  int, –年龄在20岁以下    @v_age29  int, –年龄在20~29岁    @v_age39  int, –年龄在29~39岁    @v_age49  int, –年龄在39~49岁    @v_age50  int, –年龄在49岁以上    @v_citysh int, –上海    @v_citybj int, –北京    @v_cityhz int, –杭州    @v_citygz int, –广州    @v_citysz int, –深圳    @v_cityqt int, –其他城市    @v_ejj_user int, –EJJ用户      1    @v_bjj_user int, –BJJ用户      2    @v_coun int –写入临时表的判断条件 select  –统计男 @v_man=count( case  when usersex=’1′ then 1 else null end ), –统计女 @v_women=count( case  when usersex=’2′ then 1 else null end ),  –统计20岁 @v_age20=count(case       when userAge< 20  then 1     else null     end     ) , –统计29岁 @v_age29=count(case      when userAge< 30 and userAge>19 then 1     else null     end     ), –统计39岁 @v_age39=count(case      when userAge < 40 and userAge>29 then 1     else null     end), –统计49岁 @v_age49=count(case      when userAge < 50 and userAge>39 then 1     else null     end), –统计50岁 @v_age50=count(case      when userAge >49 then 1     else null     end), –统计上海用户 @v_citysh=count(case       when city=’上海’  then 1     else null     end     ) , –统计广州用户 @v_citygz=count(case       when city=’广州’  then 1     else null     end     ), –统计北京用户 @v_citybj=count(case       when city=’北京’  then 1     else null     end     ), –统计杭州用户 @v_cityhz=count(case       when city=’杭州’  then 1     else null     end     ), –统计深圳用户 @v_citysz=count(case       when city=’深圳’  then 1     else null     end     ) , —-统计除已上之外的其他用户 @v_cityqt=count(case       when city not in (‘北京’,’广州’,’上海’,’杭州’,’深圳’)  then 1     else null     end     )  from (select *               from tb_gl_userInf              where registertype = 4                 and usertype&4=4                and CONVERT(char(10), registertime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)             union all             select *               from tb_gl_userInf              where registertype !=4                 and usertype&4 = 4                and CONVERT(char(10), mulregtime,20)  = CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a   –统计ping码上行数据tb_gl_userreplycommand   –ping码上行总计     select @v_ping=count(*)     from (select *            from tb_gl_userreplycommand            where motype = 3            and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))  a   –发送ping码且发送EJJ用户数   select @v_ping_ejj=count(*)   from (select *         from tb_gl_userreplycommand         where motype = 3         and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))  a      where telephone in         (select telephone from tb_gl_userreplycommand where motype = ‘2’)   –发送ping码且发送BJJ用户数   select @v_ping_bjj=count(*)     from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))  a     where telephone in          (select telephone from tb_gl_userreplycommand where motype = ‘1’);   –发送ping码且非EJJ/BJJ（consumer）用户    set @v_ping_con=  @v_ping –  @v_ping_ejj –  @v_ping_bjj;   –发送ping码并注册的用户   select @v_ping_reg=count(distinct telephone)     from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a     where telephone in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))   –发送ping码并注册且发送过EJJ的用户   select @v_ping_ejj=count(distinct telephone)     from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a    where telephone in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)      and telephone in          (select telephone from tb_gl_userreplycommand where motype = ‘2’))   –发送ping码并注册且发送过BJJ的用户   select @v_ping_reg_bjj=count(distinct telephone)    from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) <=CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a    where telephone in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)      and telephone in          (select telephone from tb_gl_userreplycommand where motype = ‘1’))   –发送ping码并注册但不含EJJ/BJJ用户(consumer)    set @v_ping_reg_ejj=0    set @v_ping_reg_con=  @v_ping_reg –  @v_ping_reg_ejj –  @v_ping_reg_bjj;   –发送ping码未注册的用户   select @v_ping_noreg=count(distinct telephone)     from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a    where telephone not in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20))   –发送ping码未注册且发送过EJJ的用户   select @v_ping_noreg_ejj=count(distinct telephone)      from (select *             from tb_gl_userreplycommand             where motype = 3             and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a    where telephone in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)      and telephone in          (select telephone from tb_gl_userreplycommand where motype = ‘2’))   –发送ping码未注册且发送过BJJ的用户   select @v_ping_noreg_bjj=count(distinct telephone)     from (select *             from tb_gl_userreplycommand            where motype = 3              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)) a    where telephone in          (select telephone             from tb_gl_userreplycommand            where motype = 4              and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20)      and telephone in          (select telephone from tb_gl_userreplycommand where motype = ‘1’))   –发送ping码未注册未发送过ejj/bjj的用户   set @v_ping_noreg_con=  @v_ping_noreg –  @v_ping_noreg_ejj –  @v_ping_noreg_bjj;   –统计EJJ用户         select @v_ejj_user=count(*)     from tb_gl_userreplycommand    where moType = ‘2’      and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20) –统计BJJ用户   select @v_bjj_user=count(*)     from tb_gl_userreplycommand    where moType = ‘1’      and CONVERT(char(10), sendtime,20) =CONVERT(char(10), dateadd(dd,-1,getdate()),20) —  select @v_coun =count(*)  from t_gnhd_temp where datetime = CONVERT (char(10), getdate() ,20) —  if  @v_coun = 0      –begin      insert into tb_report_gnfl       (ping,        ping_ejj,        ping_bjj,        ping_con,        ping_reg,        ping_reg_ejj,        ping_reg_bjj,        ping_reg_con,        man,        women,        age20,        age29,        age39,        age49,        age50,        citysh,        citybj,        cityhz,        citygz,        citysz,        cityqt,        ping_noreg,        ping_noreg_ejj,        ping_noreg_bjj,        ping_noreg_con,        ejj_user,        bjj_user,        datetime)     values(     @v_ping,         @v_ping_ejj,         @v_ping_bjj,         @v_ping_con,         @v_ping_reg,         @v_ping_reg_ejj,         @v_ping_reg_bjj,         @v_ping_reg_con,         @v_man,         @v_women,         @v_age20,         @v_age29,         @v_age39,         @v_age49,         @v_age50,         @v_citysh,         @v_citybj,         @v_cityhz,         @v_citygz,         @v_citysz,         @v_cityqt,         @v_ping_noreg,         @v_ping_noreg_ejj,         @v_ping_noreg_bjj,         @v_ping_noreg_con,         @v_ejj_user,         @v_bjj_user,     CONVERT(char(10), dateadd(dd,-1,getdate()),20)) GO SET QUOTED_IDENTIFIER OFF  GO SET ANSI_NULLS ON  GO

发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/100571.html原文链接：