Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given that there are two dates like 2012-07-08 and 2013-02-06. I need to count record of each user for every month. If there is no data for a user in particular month then it should return zero. The output should look like:-

id name   July-12 August-12 September-12 October-12 November-12 December-12 January-13 February-13
1  John      1        2          0            0         1           1           2      2    
2  David     0        1          0            1         1           1           2      2
3  Marry     2        1          0            0         0           1           2      2
share|improve this question

closed as off-topic by jcolebrand Jul 8 '13 at 23:36

If this question can be reworded to fit the rules in the help center, please edit the question.

    
Define "every month". Are you only interested in the range in your demo or should the query provide each month range represented in the data? –  Paul Jul 8 '13 at 14:16
5  
Please do not cross-post: stackoverflow.com/questions/17523763/… –  ypercube Jul 8 '13 at 17:51
    
This question appears to be off-topic because it is cross-posted. –  jcolebrand Jul 8 '13 at 23:36

1 Answer 1

Since you need to include months that are not present in the data, you have to get creative to find the months between the two dates. I usually see this done using a recursive CTE (shown below) or a numbers/tally table.

The next challenge is to make each of these months its own column, which calls for a dynamic pivot:

declare @start date = '2012-07-08';
declare @end date = '2013-02-06';
declare @columns as nvarchar(max);
declare @sql as nvarchar(max);
with cte as (
    select
        d = @start
        ,m = datename(month,@start)+ '-' + right(datename(year,@start),2)
    union all
    select
        dateadd(month,1,d)
        ,datename(month,dateadd(month,1,d))+ '-' + right(datename(year,dateadd(month,1,d)),2)
        from cte
        where dateadd(month,datediff(month,0,dateadd(month,1,d)),0) <= @end
    )
select @columns = stuff((
    select
        ',' + quotename(m)
        from cte
        order by d
        for xml path('')
    ),1,1,'')
    ;
set @sql = N'
    select
        id
        ,name
        ,' + @columns + '
        from (
            select
                id
                ,name
                ,m = datename(month,dateadd(month,1,fooDate))+ ''-'' + right(datename(year,dateadd(month,1,fooDate)),2)
                from foo
                where fooDate >= ' + cast(@start as nvarchar(10)) + '
                    and fooDate <= ' + cast(@end as nvarchar(10)) + '
            ) pdata
        pivot (count(id) for m in (' + @columns + ') pvt';
print @sql;
--execute(@sql);

You don't mention your version; both recursive CTEs and pivots are not available prior to SQL Server 2005, and some formatting used requires 2008 or later.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.