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
IF @ChannelID '0'
BEGIN
SET @sql1 =@sql1 + ' AND ChannelID='''+@ChannelID+''''
END
集企业自助建站、网络营销、商品推广于一体的系统 功能说明： 1、系统采用Microsoft SQL Server大型数据库支持，查询数据库用的全是存储过程，速度和性能极好。开发环境是vs.net，采用4层结构，具有很好的可维护性和可扩冲性。 2、用户注册和登陆 未注册用户只具备浏览商品、新闻和留言功能；要采购商品，需接受服务协议并填写相关注册信息成为正式用户后方可进行，以尽可能减少和避免无效
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
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
