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
    
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
add comment

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.

1 Answer

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
add comment

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