0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for gardeners and landscapers

DECLARE @PostID int,
        @StartMonth int,
        @StartYear int,
        @EndMonth int,
        @EndYear int
        
SET @PostID = ##PostID:int## --4228356   

SELECT @StartMonth  = Month(CreationDate),
       @StartYear  = Year(CreationDate)
FROM
       Posts 
WHERE
       Id = @PostID
       
SELECT @EndMonth = Month(Max(CreationDate)) ,
       @EndYear =  Year(Max(CreationDate)) 
FROM Posts


;WITH monthyear 
     AS (SELECT 
                 row_number() over (ORDER BY  years.NUMBER, months.NUMBER) rn,
                years.NUMBER  AS [Year], 
                months.NUMBER AS [Month] 
         FROM   MASTER..spt_values months 
                CROSS JOIN MASTER..spt_values years 
         WHERE  months.NUMBER BETWEEN 1 AND 12 
                AND months.TYPE = 'P' 
                AND years.TYPE = 'P' 
                AND years.NUMBER BETWEEN @StartYear AND @EndYear) 

SELECT my.[Year], 
       my.[Month], 
       vt.name, 
       COUNT(vt.id) votecount 
FROM   monthyear my 
       LEFT JOIN (votes v 
                  INNER JOIN votetypes vt 
                    ON v.votetypeid = vt.id) 
         ON my.[Month] = MONTH(v.creationdate) 
            AND my.[Year] = YEAR(v.creationdate) 
            AND v.postid = @PostID 
WHERE
         my.rn between 
                 (SELECT MIN(rn) FROM  monthyear WHERE [Month] = @StartMonth)
                 AND
                 (SELECT MAX(rn) FROM  monthyear WHERE [Month] = @EndMonth)
GROUP  BY my.[Year], 
          my.[Month], 
          vt.name 
ORDER BY          
         my.[Year], 
         my.[Month], 
         vt.name

fork query

edited apr 18 12

created apr 18 12 conradfrix