SQL Tag Cloud

sqltagcloud.sql

declare @MaxTagFrequency int
declare @tt_Tags table(
        BlogCategory_ID int,
        [Count] int
)
 
insert into
        @tt_Tags
select top(@CountLimit)
        bc.BlogCategory_ID,
        count(bc.BlogCategory_ID)
from
        BlogCategory bc
        join BlogEntryCategory bec on bc.BlogCategory_ID = bec.BlogCategory_ID
group by
        bc.BlogCategory_ID,
        bc.Category
having count(bc.BlogCategory_ID) > 0
 
select @MaxTagFrequency = max([Count]) from @tt_Tags
 
select
        tt.BlogCategory_ID,
        bc.Category,
        tt.[Count],
        convert(decimal(6,2),(convert(float,tt.[Count]) / convert(float,@MaxTagFrequency)) * 100) as [Weight]
from
        @tt_Tags tt
        join BlogCategory bc on tt.BlogCategory_ID = bc.BlogCategory_ID
order by
        bc.Category