Hey, RobVolk (or others): Dynamic crosstab, snitz

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Hey, RobVolk (or others): Dynamic crosstab, snitz

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-01-14 : 03:32:37

Ok, so I'm trying to get a nice graph of the activity in various snitz forums over time. I've got this nifty SP which returns the relevant data:

CREATE PROCEDURE p_PostsByWeek AS
set nocount on
SELECT dateadd(week,week - 1,convert(varchar(4),year) + '-01-01 00:00:00.000') , A.forum_id,f_subject,Count(*) Posts 
FROM 
(SELECT datepart(year,datetime) Year, datepart(week,datetime) Week,forum_id
FROM snitz_topics  WITH(NOLOCK)
UNION ALL 
SELECT datepart(year,datetime) Year, datepart(week,datetime) Week ,forum_id
FROM snitz_reply WITH(NOLOCK)) AS A 
join snitz_forum on snitz_forum.forum_id=A.Forum_Id
GROUP BY A.Year, A.Week,A.forum_id,f_subject
ORDER BY A.Year, A.Week,A.forum_id,f_subject
GO

...it works nicely. However, for any given week, there are forums that didn't get any posts (when a forum is added, all previous weeks just don't exist).

For graphing purposes, it would be really nice to have a simple crosstab of datetime versus forum ID, with number of posts as the data. I can't, for the life of me, get this query into crosstab format.

The only other idea I had was maybe using a temp table for the weeks, and joining this query against it with an IsNull to get 0 if the forum doesn't exist for that week, if that makes any sense. But I'm irked at not being able to get a crosstab going on this query.

So, RobVolk, I came across your nice article http://www.sqlteam.com/item.asp?ItemID=2955 , and it says something about improving it to handle subqueries (which I've got). Any luck on that?

Any other suggestions, anyone? This would be pretty handy for anyone running Snitz, you know [:]

Cheers
-b

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-01-14 : 04:11:53

A temporary solution while waiting for the answers you really want could be to use a temp table, fill it up and then use the crosstab SP on that.

aiken
Aged Yak Warrior

525 Posts

Posted - 2003-01-14 : 16:55:33

Good point, Andraax. With that suggestion, I have created what is probably the least efficient way to do this, but it does at least work and return the proper values, suitable for most graphing applications. Here goes:

CREATE PROCEDURE p_PostsByWeek AS
set nocount on

create table #PostData
(dWeek datetime,
 forum_id int,
 Posts int)

create table #PivotSource
(dWeek datetime,
 forum_id int,
 Posts int)

create table #Weeks (dWeek datetime)
create table #Forums (forum_id int)
create table #ForumWeeks (dWeek datetime,forum_id int)

DECLARE @vcPivotSQL varchar(2000)

insert into #PostData (dWeek,forum_id,Posts)
SELECT dateadd(week,week - 1,convert(varchar(4),year) + '-01-01 00:00:00.000') , A.forum_id,Count(*) Posts 
FROM 
(SELECT datepart(year,datetime) Year, datepart(week,datetime) Week,forum_id
FROM snitz_topics  WITH(NOLOCK)
UNION ALL 
SELECT datepart(year,datetime) Year, datepart(week,datetime) Week ,forum_id
FROM snitz_reply WITH(NOLOCK)) AS A 
GROUP BY A.Year, A.Week,A.forum_id
ORDER BY A.Year, A.Week,A.forum_id

insert into #Weeks (dWeek)
  select distinct dWeek from #PostData

insert into #Forums(forum_id)
  select distinct forum_id from #PostData

insert into #ForumWeeks (dWeek,forum_id)
  select #Weeks.dWeek,#Forums.Forum_id
  from #Weeks cross join #Forums

insert into #PivotSource
select #ForumWeeks.dWeek,#ForumWeeks.forum_id,IsNull(Posts,0)
 from #ForumWeeks
 left outer  join #PostData on #ForumWeeks.dWeek=#PostData.dWeek and #ForumWeeks.forum_id=#PostData.forum_id
 order by #forumWeeks.dWeek asc,#ForumWeeks.forum_id asc

exec p_crosstab 'select #PivotSource.forum_id,f_subject from #PivotSource join snitz_forum on #PivotSource.forum_id=snitz_forum.forum_id group by #PivotSource.forum_id,f_subject order by #PivotSource.forum_id','sum(posts)','dWeek','#PivotSource'

drop table #PivotSource
drop table #PostData
drop table #Weeks
drop table #Forums
drop table #ForumWeeks

GO

Cheers
-b

Edited by - aiken on 01/14/2003 16:56:27

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources