Group by Months/Weeks

Beautiful bit of code for counting valid fields and grouping the results in month/week chunks – simply replace the %%INTERVAL%% with either WEEK or MONTH (or any other valid DATEADD identifier, for that matter) and specify the table and %%DateField%% you are interested in:

    ;WITH d(d) AS 
    (
      SELECT DATEADD(%%INTERVAL%%, n, DATEADD(%%INTERVAL%%, 
                     DATEDIFF(%%INTERVAL%%, 0, @minDate), 0))
      FROM ( SELECT TOP (DATEDIFF(%%INTERVAL%%, @minDate, @maxDate) + 1) 
        n = ROW_NUMBER() OVER (ORDER BY [object_id]) - 1
        FROM sys.all_objects ORDER BY [object_id] ) AS n
    )
        SELECT     
            --COALESCE(op.Id, @ID) AS ID,
            d.d AS Date,
            count(op.%%DateField%%) AS CountOfItems
        FROM  
            d LEFT OUTER JOIN
            (select * FROM Ops o WHERE 
               o.Operation_Project = @Project_ID or @Project_ID = 1) op 
               ON op.%%DateField%% >= d.d AND 
               op.%%DateField%% < DATEADD(%%INTERVAL%%, 1, d.d)  
        GROUP BY 
            --op.Operation_Project, 
            d.d 
        HAVING 
            d.d < (SELECT MAX(o.%%DateField%%) FROM Ops o 
                   WHERE o.Id = @Project_ID or @Project_ID = 1)
        ORDER BY 
            d.d