User Trends - Static Report Fails to Run (319631)

RESOLUTION

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create procedure [CSDW_MakeUserTrends] @SiteName varchar(255), _
      @daterange varchar(300), @ResultTable varchar(36) as
Begin
	Set nocount on
	Declare @Query1 nvarchar(2000)

	SELECT @Query1 = 
	'	CREATE TABLE [dbo].['+ @RESULTTABLE + '] (
		[Site Name] [nvarchar] (256) NULL ,
		[Year] int NULL ,
		[Month] int NULL ,
		[All Users] int NULL ,
		[New Users] int NULL ,
		[Repeat Users] int NULL ,
		[Repeat User %] numeric(38,3) NULL
	) ON [PRIMARY]'
	exec (@Query1)
	SELECT @Query1 = '
	insert into ['+ @RESULTTABLE + '] 
	(
		[Site Name],
		[Year],
		[Month],
		[All Users],
		[New Users],
		[Repeat Users],
		[Repeat User %]
	)
	select
		[Site Name],
		[Year],
		[Month],
		[All Users],
		0,
		0,
		0
	from (
	SELECT SiteDimensionView.SiteName [Site Name],
	datepart(yy, dTimestamp) Year, 
	datepart(mm, dTimestamp) Month,
	Count(Distinct SQ1.AllUserKeys) [All Users] 
	FROM (
		select distinct u.SiteId, d.dTimestamp, u.LogUserID AllUserKeys   
		from UniqueUserKeyByDate u, Date d
		where u.DateID = d.DateID
		group by u.SiteId, d.dTimestamp, LogUserID 
	) as SQ1,
	SiteDimensionView 
	Where SQ1.SiteID = SiteDimensionView.SiteID
	And ' + @SiteName + '
	And ' + @DateRange + '
	Group By 
	SiteName, 
	datepart(yy, dTimestamp), 
	datepart(mm, dTimestamp)) as XXX'
	exec (@Query1)
	SELECT @Query1 = '
	Update [' + @ResultTable + ']
		SET 
			[New Users] = (select
		[NewUsers]
	from (
	Select SiteDimensionView.SiteName, 
	datepart(yy, s1.dTimestamp) Year, 
	datepart(mm, s1.dTimestamp) month, 
	count (distinct s1.LogUserID) NewUsers
	From SiteDimensionView, 
		(
		select distinct u.SiteId, u.LogUserID, min(d.dTimestamp) as dTimestamp    
		from   UniqueUserKeyByDate u, Date d
		where  u.DateID = d.DateID
		group by u.SiteId, LogUserID) as s1
	Where s1.SiteId = SiteDimensionView.SiteID
	And ' + @SiteName + '
	Group By 
	SiteDimensionView.SiteName, 
	datepart(yy, s1.dTimestamp), 
	datepart(mm, s1.dTimestamp)) as YYY
	Where YYY.SiteName = [' + @ResultTable + '].[Site Name]
	And YYY.Year = [' + @ResultTable + '].[Year]
	And YYY.month = [' + @ResultTable + '].[month])'
	exec (@Query1)
	SELECT @Query1 = '
	Update [' + @ResultTable + ']
		SET 
			[Repeat Users] = (select
		[RepeatUsers]
	from (
	Select SiteDimensionView.SiteName, 
	datepart(yy, s1.dTimestamp) Year, 
	datepart(mm, s1.dTimestamp) month, 
	count (distinct s1.LogUserID) RepeatUsers
	From SiteDimensionView, 
		(
		Select SiteId, LogUserID, d.dTimestamp  
		from UniqueUserKeyByDate u, Date d
		where  u.DateID = d.DateID
		And ' + @DateRange + '
		Group by SiteId, LogUserID, d.dTimestamp
		) as S1, 
		(
		Select SiteId, LogUserID, min(d.dTimestamp) dTimestamp
		from UniqueUserKeyByDate u, Date d
		where  u.DateID = d.DateID
		Group By SiteId, LogUserID
		) as S2 
	Where s1.dTimestamp > s2.dTimestamp
	And s1.LogUserID = s2.LogUserID
	And s1.SiteId = s2.SiteId
	And s1.SiteId = SiteDimensionView.SiteID
	And ' + @SiteName + '
	Group By 
	SiteDimensionView.SiteName, 
	datepart(yy, s1.dTimestamp), 
	datepart(mm, s1.dTimestamp)) as YYY
	Where YYY.SiteName = [' + @ResultTable + '].[Site Name]
	And YYY.Year = [' + @ResultTable + '].[Year]
	And YYY.month = [' + @ResultTable + '].[month])'
	exec (@Query1)
	SELECT @Query1 = '
	Update [' + @ResultTable + ']
		SET [Repeat User %] _
                  = 100*(Convert(float, [Repeat Users])/Convert(float, [All Users]))'
	exec (@Query1)
END
GO