12

0

Please login or register to vote for this query.

(click on this box to dismiss)

Joomla Meta

Q&A about the site for Joomla! administrators, users, developers and designers

with Date as (
    SELECT CAST('2012-7-01' as date) DateId
    UNION ALL
    SELECT DATEADD(day, 1, DateId)
    FROM Date
    WHERE DATEADD(day, 1, DateId) <= '2012-7-25'
    ) ,
    
    Comments2 as (
    SELECT Id,PostId,CONVERT(date, CreationDate) as cDate
    FROM Comments
    ) 
    
select d.DateId, c.PostId, Count(c.Id) as CommentCount

from Date d LEFT JOIN Comments2 c
on d.DateId=c.cDate
where  d.DateId >= '2012-07-01' AND d.DateId <= '2012-07-25' 
group by d.DateId, c.PostId
order by d.DateId asc, Count(c.Id) desc


/*RANK ( ) OVER ( [ d.DateId, c.PostId] order by Count(c.Id) desc )  */

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results