count visitore for year, month, week by IP and date

Question

Hi

I have a table like this :

Id, Ip, DateTime

63267	66.249.71.209	2011-12-17 16:52:28.967
63266	66.249.71.209	2011-12-17 16:52:26.247
63265	66.249.71.209	2011-12-17 16:52:25.890
63264	66.249.71.209	2011-12-17 16:52:25.590
63263	91.99.225.136	2011-12-17 16:51:38.343
63262	180.76.5.100	2011-12-17 16:51:37.297
63261	66.249.71.209	2011-12-17 16:51:33.030

 

From these data I want to extract some information like:

1-      Number of visitors from beginning of current year to now

2-      Number of visitors during last month ( last 30 days) to now

3-      Number of visitors during last month (current month) to now

4-      Number of visitors during last week ( last 7 days) to now

I have this procedure and I thought it is correct, but it is wrong.

May you correct it? It calculates distinct IP while it must consider Date too. I tried with group by but no result.

Thanks

USE [parsnew24db]

GO

/****** Object:  StoredProcedure [dbo].[Statistics_Get]    Script Date: 12/17/2011 13:13:01 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Statistics_Get]

(@StatisticsParams varchar(200))

AS

BEGIN

 

DECLARE @x TABLE (ID int, Data nvarchar(100));

insert into @x select * from dbo.fnc_Split(@StatisticsParams,',')

 

--Visitors in this year

if (select data from @x where ID=1)=1

begin

declare @VinY bigint

select @VinY=count(Distinct(ip)) from [Statistics].StatisticsForModule where DATEPART(year, [DateTime])= DATEPART(year, getdate())

UPDATE @x 

SET Data = @VinY

FROM @x AS x 

where x.Id=1

end

 

--Visitors in last month

if (select data from @x where ID=2)=1

begin

      Declare @LastMonthFirstDay datetime, @EndDate datetime

      Set @LastMonthFirstDay =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)

      SET @EndDate = dateadd(mm, 1, @LastMonthFirstDay)

      UPDATE @x 

      SET Data = (SELECT COUNT(distinct(Ip)) FROM [Statistics].[StatisticsForModule] WHERE [DateTime] BETWEEN @LastMonthFirstDay AND @EndDate)

      FROM @x AS x 

      where x.Id=2

end

 

--Total visitors of a last month

if (select data from @x where ID=3)=1

begin

      --Declare @LastMonthFirstDay datetime

      Set @LastMonthFirstDay =  DATEADD(MONTH, DATEDIFF(MONTH, 0, CURRENT_TIMESTAMP) - 1, 0)

      UPDATE @x 

      SET Data = (SELECT COUNT(distinct(Ip)) FROM [Statistics].[StatisticsForModule] WHERE [DateTime] BETWEEN @LastMonthFirstDay AND getdate())

      FROM @x AS x 

      where x.Id=3

end

 

      --SET Data = (SELECT COUNT(distinct(Ip)) FROM [Statistics].[StatisticsForModule] WHERE [DateTime] BETWEEN @Lastweek AND getdate())

--Total Visitors week

if (select data from @x where ID=4)=1

begin

      Declare @Lastweek datetime

      Set @Lastweek =  DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 7, 0)

      UPDATE @x 

      SET Data = (SELECT COUNT(distinct(IP)) FROM [Statistics].[StatisticsForModule] WHERE [DateTime] BETWEEN @Lastweek AND getdate())

      FROM @x AS x 

      where x.Id=4

end

 

--Total Visitors Yesterday

if (select data from @x where ID=5)=1

begin

      UPDATE @x 

      SET Data = (SELECT COUNT(distinct(Ip)) FROM [Statistics].[StatisticsForModule] WHERE convert(varchar(10),[DateTime],111) = convert(varchar(10) , DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP) - 1, 0),111))

      FROM @x AS x 

      where x.Id=5

end

 

--Total Visitors Today

if (select data from @x where ID=6)=1

begin

      UPDATE @x 

      SET Data = (SELECT COUNT(distinct(Ip)) FROM [Statistics].[StatisticsForModule] WHERE convert(varchar(10),[DateTime],111) = convert(varchar(10) , getdate(),111))

      FROM @x AS x 

      where x.Id=6

end

 

 

select * from @x

 

END

 

 

Answer 1

Don't cross-post questions. See my answer in Transact-SQL.