Archive

Archive for February, 2013

Monthly tab data display with grouping

February 5, 2013 Leave a comment

select c.Name as Client,c.ClientName,
sum(case when month(o.CreatedDate) = 1 then 1 else 0 end) as Jan,
sum(case when month(o.CreatedDate) = 2 then 1 else 0 end) as Feb,
sum(case when month(o.CreatedDate) = 3 then 1 else 0 end) as Mar,
sum(case when month(o.CreatedDate) = 4 then 1 else 0 end) as Apr,
sum(case when month(o.CreatedDate) = 5 then 1 else 0 end) as May,
sum(case when month(o.CreatedDate) = 6 then 1 else 0 end) as Jun,
sum(case when month(o.CreatedDate) = 7 then 1 else 0 end) as Jul,
sum(case when month(o.CreatedDate) = 8 then 1 else 0 end) as Aug,
sum(case when month(o.CreatedDate) = 9 then 1 else 0 end) as Sep,
sum(case when month(o.CreatedDate) = 10 then 1 else 0 end) as Oct,
sum(case when month(o.CreatedDate) = 11 then 1 else 0 end) as Nov,
sum(case when month(o.CreatedDate) = 12 then 1 else 0 end) as Dec,
count(o.ColumnName) as Total
from table1 o with (nolock)
inner join table2 c on c.ID=o.ID
where o.CreatedDate between @StartDate and @EndDate
and c.Id= @ID
group by c.Name

Categories: SQL