SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cs_UserActivityNightlyJob]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[cs_UserActivityNightlyJob]
GO

CREATE     PROCEDURE [dbo].cs_UserActivityNightlyJob

AS
SET Transaction Isolation Level Read UNCOMMITTED
BEGIN
	declare @RangeDate datetime
	declare @JobRunDate datetime
	set @JobRunDate = getdate()
	set @RangeDate = dateadd(d, -1, convert(datetime, (convert(varchar(10), @JobRunDate, 101))))
	
	insert into [cs_VisitsDaily]
	select
		v.IP
		,VisitCount  = (select sum(v1.VisitCount) from cs_Visits v1 where (v1.IP = v.IP) and (v1.DateTimeStamp between @RangeDate and dateadd(d, 1, @RangeDate)) group by v1.IP)
		,VisitDate = @RangeDate
		,JobDate = @JobRunDate
	from
		cs_Visits v
	where
		v.DateTimeStamp between @RangeDate and dateadd(d, 1, @RangeDate)
	group by
		v.IP

	---------------------------------------------
	
	DELETE cs_Visits WHERE cs_Visits.DateTimeStamp between @RangeDate and dateadd(d, 1, @RangeDate)
END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

grant execute on [dbo].cs_UserActivityNightlyJob to public
go
