0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for members of the workforce navigating the professional setting

select 
A.PostId as PostId,
D.DateId as DateDDMMYYYY,
count(C1.Id)
----Assuming that there is a date_ddmmyyyy column on the date dimension table

from
(select
top 50
C.PostId
from
Comments C
where DATEDIFF(day,C.CreationDate , GETDATE()) <= 365
group by C.PostId
order by count(C.Id) desc
) A

join

Comments C1
on A.PostId = C1.PostId

join

(
select
C2.CreationDate,
CONVERT(date, C2.CreationDate) as DateId 
from Comments C2
) D
on C1.CreationDate = D.CreationDate
----Replicating a date dimension table

group by
A.PostId,
D.DateId

order by
D.DateId
;

fork query

edited feb 3

created feb 2 anonymous