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