majaro
06-08-2009, 12:30 PM
I'm having trouble with counting the number of times an article has been viewed. The table I am working with has three fields; 2 defined as type CHAR and 1 as type DATE. The code I am trying to use is below. Basically what it does is check the field DateViewed against todays date giving the number of days difference. What I cannot figure out is the following line:
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 0 and 10 THEN ???? ELSE 0 END) AS 'D10',
The then part should be some kind of code to add 1 to some kind of counter or INT variable which would be displayed in the resulting pivot table. Here is the whole code:
Select ArticleViewed, D10,D20,D30 FROM (
SELECT
DATEDIFF(now(), DateViewed), ArticleViewed ,
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 0 and 10 THEN Something ELSE 0 END) AS 'D10',
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 11 and 20 THEN Something ELSE 0 END) AS 'D20',
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 21 and 30 THEN Something ELSE 0 END) AS 'D30'
from ArticleViews
Group by ArticleViewed
Order by ArticleViewed desc
) AS tmp;
Thanks in advance for any help.
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 0 and 10 THEN ???? ELSE 0 END) AS 'D10',
The then part should be some kind of code to add 1 to some kind of counter or INT variable which would be displayed in the resulting pivot table. Here is the whole code:
Select ArticleViewed, D10,D20,D30 FROM (
SELECT
DATEDIFF(now(), DateViewed), ArticleViewed ,
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 0 and 10 THEN Something ELSE 0 END) AS 'D10',
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 11 and 20 THEN Something ELSE 0 END) AS 'D20',
SUM(CASE WHEN DATEDIFF(now(), DateViewed) between 21 and 30 THEN Something ELSE 0 END) AS 'D30'
from ArticleViews
Group by ArticleViewed
Order by ArticleViewed desc
) AS tmp;
Thanks in advance for any help.