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原文链接:

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SAS Proc transpose过程步

    什么是转置?转置其实就是数据结构的转换,将横向的结构转成纵向的结构,或将纵向转向横向。

    Setup
  • 浅谈:PROC FORMAT过程步(快速生成format)

    Setup
  • 存储过程

    ## 优点 1. 更好性能  * 存储过是预编译的,在创建时编译;一般SQL是每次执行都会编译 2. 功能实现更加灵活  * 存储过程可以使用条件判断和游...

    苦咖啡
  • 存储过程

    什么是存储过程:存储过程可以说是一个记录集吧,它是由一些T-SQL语句组成的代码块,这些T-SQL语句代码像一个方法一样实现一些功能(对单表或多表的增删改查),...

    用户2038009
  • 存储过程

    宇宙之一粟
  • 存储过程

    存储过程是用于将代码存储在数据库端,通过存储过程名称就可以调用。存储过程类似于java的方法,但是也是有区别的,方法只能返回一个值,并且需要声明返回值的类型,但...

    端碗吹水
  • 存储过程

    今日小知识点:(sql 的书写规则) 1、不区分大小写 2、一条语句可以写在一行上也可以多行 3、可用注释增强语句的可读性:单行注释(--)、多行注释(/*.....

    ellipse
  • 存储过程

    为了保证数据的完整性、一致性,提高应用性能,常采用存储过程技术。 一个存储过程包括名字、参数列表、及许多SQL语句的语句集。

    cherishspring
  • MS SQL 的存储过程练习

    /*带参存储过程 if(OBJECT_ID('proc_find_stu', 'p') is not null)    dro...

    静谧的小码农

扫码关注云+社区

领取腾讯云代金券