USE Northwind
GO

CREATE TABLE myTallyArticle (col1 int)
GO

SET NOCOUNT ON
DECLARE @ctr int
SELECT @ctr = 1 

WHILE @ctr < 1000
	BEGIN 
		INSERT INTO myTallyArticle(col1) SELECT @ctr
		SELECT @ctr = @ctr + 1  
	END
SET NOCOUNT OFF
GO

CREATE TABLE myTallyRating (col1 int)
GO

INSERT INTO myTallyRating (col1) 
SELECT 1 UNION ALL SELECT 2  UNION ALL SELECT 3 UNION ALL SELECT 4  UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7  UNION ALL SELECT 8 UNION ALL SELECT 9  UNION ALL SELECT 10
GO


CREATE TABLE myTable99 (ArticleId Int, Rating int, RatingDate datetime)
GO

SET NOCOUNT ON
DECLARE @ctr int, @article int, @rating int

SELECT @ctr = 1

--TODAY

WHILE @ctr < 100
	BEGIN
		SELECT  TOP 1 @Article = col1 FROM myTallyArticle Order by NEWID()

		-- Couldn't get this to work, always reurns 1
		-- SELECT TOP 1 col1 FROM myTallyRating Order by NEWID()

		SELECT @Rating = CASE WHEN CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1 > 10 THEN 10
	    		              ELSE CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1
       		                 END
		INSERT INTO myTable99 (ArticleId, Rating, RatingDate) SELECT @Article, @Rating, GetDate()
		
		SELECT @ctr = @ctr + 1
	END	

--WEEK

WHILE @ctr < 1000
	BEGIN
		SELECT TOP 1 @Article = col1 FROM myTallyArticle Order by NEWID()

		-- Couldn't get this to work, always reurns 1
		-- SELECT TOP 1 col1 FROM myTallyRating Order by NEWID()

		SELECT @Rating = CASE WHEN CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1 > 10 THEN 10
	    		              ELSE CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1
       		                 END
		INSERT INTO myTable99 (ArticleId, Rating, RatingDate) SELECT @Article, @Rating, DATEADD(wk, -1,GetDate())
		
		SELECT @ctr = @ctr + 1
	END	
--MONTH

WHILE @ctr < 10000
	BEGIN
		SELECT TOP 1 @Article = col1 FROM myTallyArticle Order by NEWID()

		-- Couldn't get this to work, always reurns 1
		-- SELECT TOP 1 col1 FROM myTallyRating Order by NEWID()

		SELECT @Rating = CASE WHEN CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1 > 10 THEN 10
	    		              ELSE CONVERT(int,SUBSTRING(RIGHT(CONVERT(varchar(26),GetDate(),126),3),3,1))+1
       		                 END
		INSERT INTO myTable99 (ArticleId, Rating, RatingDate) SELECT @Article, @Rating, DATEADD(mm, -1,GetDate()) 
		
		SELECT @ctr = @ctr + 1
	END	
SET NOCOUNT ON
GO


  SELECT TOP 10
	 ArticleId, Sum(Rating)/Count(Rating) As Avg_Rating, Count(Rating) As Num_Ratings
    FROM myTable99
   WHERE DATEDIFF(d,RatingDate,GetDate()) = 0 
GROUP BY ArticleId 
ORDER BY 2 DESC

  SELECT TOP 10
	 ArticleId, Sum(Rating)/Count(Rating) As Avg_Rating, Count(Rating) As Num_Ratings
    FROM myTable99
   WHERE DATEDIFF(w,RatingDate,GetDate()) <= 1 
GROUP BY ArticleId 
ORDER BY 2 DESC

  SELECT TOP 10
	 ArticleId, Sum(Rating)/Count(Rating) As Avg_Rating, Count(Rating) As Num_Ratings
    FROM myTable99
   WHERE DATEDIFF(m,RatingDate,GetDate()) <= 1 
GROUP BY ArticleId 
ORDER BY 2 DESC
GO

DROP TABLE myTallyArticle
GO

DROP TABLE myTableRating
GO

DROP TABLE myTable99
GO

quote:

---

I'd keep it simple...The table I gave you is all you should need.

If you add all of what you discuss you'll complicate things and logically it may be hard to follow.

Also, the things you are discussing are more in the realm of data warehousing where all "facts and dimensions" are calculated and stored and never change...which is not a bad thing.

Do you have Enterprise Edition?



Brett

8-)

---

quote:

---

You really ought to call yourself 'SQL Scrub'.

How you can find the energy to write so much code for other people I don't know. Do you work part-time or is it just a compulsive desire to write SQL?

Nice to see you using Tally tables though.

Regards.

Shadow to Light

---

quote:

---

You are overemphasizing the supposed negative effects of "large" rowset sizes. 500K+ rows is hardly anything when the rows are narrow (i.e. a relatively low # of bytes / row).

If you had 500 million rows, that would be something else.

Rather than engineer yourself into paralysis, try a simple schema with no denormalization and write the queries you need. If the performance is inadequate, come back to us.

Jonathan
{0}

---