set ansi_nulls on
set quoted_identifier on
go
-- =============================================
-- author:wgh
-- create date: 2012-06-07
-- description: ip访问预统计
-- =============================================
create procedure [dbo].[proc_hb_prestatbyip]
    @begintime varchar(50),
    @endtime varchar(50),
    @timetype int,    --0代表小时  、 1代表天
    @channelid varchar(10)='0',
    @ipaddress varchar(20)
as
begin
    declare @sql1 nvarchar(2000)
    if @timetype = 0
    begin
set @sql1='select convert(varchar(13),addtime,120) as ''日期''
                  ,channelid as ''推广渠道''
                  ,ipaddress as ''ip地址''
                  ,viewcount as ''浏览次数''
                  ,clickcount as ''点击次数''
                  from dbo.hb_adprestatip where addtime>='''+@begintime+''' and addtime<='''+@endtime+''''
if @channelid <> '0'
        begin
           set @sql1 =@sql1 + ' and channelid='''+@channelid+''''
        end
        if @ipaddress <> ''
        begin
          set @sql1 =@sql1 +' and ipaddress='''+@ipaddress+''''
        end
        set @sql1 =@sql1+' order by viewcount desc'
    end
    else
    begin
set @sql1='select convert(varchar(10),addtime,120) as ''日期''
                          ,channelid as ''推广渠道''
                          ,ipaddress as ''ip地址''
                          ,sum(viewcount)  as ''浏览次数''
                          ,sum(clickcount)  as ''点击次数'' 
                    from dbo.hb_adprestatip where addtime>='''+@begintime+''' and addtime<='''+@endtime+''''
if @channelid > '0'
        begin
           set @sql1 =@sql1 + ' and channelid='''+@channelid+''''
        end
        if @ipaddress <> ''
        begin
          set @sql1 =@sql1 +' and ipaddress='''+@ipaddress+''''
        end
set @sql1 =@sql1 + 'group by convert(varchar(10),addtime,120),channelid,ipaddress order by sum(viewcount) desc'
    end
   exec sp_executesql @sql1 
end