PDA

View Full Version : Pivot Tables question



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.

CHatmaker
07-20-2009, 12:09 PM
SELECT
ArticleViewed,
SUM(CASE WHEN DATEDIFF(Day, DateViewed, Getdate()) between 0 and 10 THEN 1 ELSE 0 END) AS 'D10',
SUM(CASE WHEN DATEDIFF(Day, DateViewed, Getdate()) between 11 and 20 THEN 1 ELSE 0 END) AS 'D20',
SUM(CASE WHEN DATEDIFF(Day, DateViewed, Getdate()) between 21 and 30 THEN 1 ELSE 0 END) AS 'D30'

FROM ArticleViews

Group by ArticleViewed
Order by ArticleViewed desc


Hope this helps. I made some assumptions that may not be right. I assumed:

Each record in your table represents a single view
You're using Transact SQL to retrieve the data
ArticleViewed and DateViewed are columns/fields in your table/fileI didn't see the need for a subselect so maybe I missed something there. I'm puzzled also by the ORDER BY. I suspect you want a grand total and sort by that.