datepart(m,[Date Field])
instead of
month([Date Field])
use DB_Mydatabase
select
[Company],
(case when month([Date Field]) = 1 then count(*) else 0 end) as Jan,
(case when month([Date Field]) = 2 then count(*) else 0 end) as Feb,
(case when month([Date Field]) = 3 then count(*) else 0 end) as Mar,
(case when month([Date Field]) = 4 then count(*) else 0 end) as Apr,
(case when month([Date Field]) = 5 then count(*) else 0 end) as May,
(case when month([Date Field]) = 6 then count(*) else 0 end) as Jun,
(case when month([Date Field]) = 7 then count(*) else 0 end) as Jul,
(case when month([Date Field]) = 8 then count(*) else 0 end) as Aug,
(case when month([Date Field]) = 9 then count(*) else 0 end) as Sep,
(case when month([Date Field]) = 10 then count(*) else 0 end) as Oct,
(case when month([Date Field]) = 11 then count(*) else 0 end) as Nov,
(case when month([Date Field]) = 12 then count(*) else 0 end) as Dec,
Count(*) as TotalYear
from
dbo.MySourceTable
group by
[Company],
[Date Field]
Order by
TotalYear
select
[Company],
Sum(case when month([Date Field]) = 1 then 1 else 0 end) as Jan,
Sum(case when month([Date Field]) = 2 then 1 else 0 end) as Feb,
Sum(case when month([Date Field]) = 3 then 1 else 0 end) as Mar,
Sum(case when month([Date Field]) = 4 then 1 else 0 end) as Apr,
Sum(case when month([Date Field]) = 5 then 1 else 0 end) as May,
Sum(case when month([Date Field]) = 6 then 1 else 0 end) as Jun,
Sum(case when month([Date Field]) = 7 then 1 else 0 end) as Jul,
Sum(case when month([Date Field]) = 8 then 1 else 0 end) as Aug,
Sum(case when month([Date Field]) = 9 then 1 else 0 end) as Sep,
Sum(case when month([Date Field]) = 10 then 1 else 0 end) as Oct,
Sum(case when month([Date Field]) = 11 then 1 else 0 end) as Nov,
Sum(case when month([Date Field]) = 12 then 1 else 0 end) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
Sum(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else 0 end) as Jan,
Sum(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else 0 end) as Feb,
Sum(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else 0 end) as Mar,
Sum(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else 0 end) as Apr,
Sum(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else 0 end) as May,
Sum(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else 0 end) as Jun,
Sum(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else 0 end) as Jul,
Sum(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else 0 end) as Aug,
Sum(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else 0 end) as Sep,
Sum(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else 0 end) as Oct,
Sum(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else 0 end) as Nov,
Sum(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else 0 end) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
count(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else 0 end) as Jan,
count(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else 0 end) as Feb,
count(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else 0 end) as Mar,
count(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else 0 end) as Apr,
count(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else 0 end) as May,
count(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else 0 end) as Jun,
count(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else 0 end) as Jul,
count(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else 0 end) as Aug,
count(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else 0 end) as Sep,
count(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else 0 end) as Oct,
count(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else 0 end) as Nov,
count(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else 0 end) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
count(case when month(convert(varchar,[Date Field],120)) = 1 then 1 else NULL END) as Jan,
count(case when month(convert(varchar,[Date Field],120)) = 2 then 1 else NULL END) as Feb,
count(case when month(convert(varchar,[Date Field],120)) = 3 then 1 else NULL END) as Mar,
count(case when month(convert(varchar,[Date Field],120)) = 4 then 1 else NULL END) as Apr,
count(case when month(convert(varchar,[Date Field],120)) = 5 then 1 else NULL END) as May,
count(case when month(convert(varchar,[Date Field],120)) = 6 then 1 else NULL END) as Jun,
count(case when month(convert(varchar,[Date Field],120)) = 7 then 1 else NULL END) as Jul,
count(case when month(convert(varchar,[Date Field],120)) = 8 then 1 else NULL END) as Aug,
count(case when month(convert(varchar,[Date Field],120)) = 9 then 1 else NULL END) as Sep,
count(case when month(convert(varchar,[Date Field],120)) = 10 then 1 else NULL END) as Oct,
count(case when month(convert(varchar,[Date Field],120)) = 11 then 1 else NULL END) as Nov,
count(case when month(convert(varchar,[Date Field],120)) = 12 then 1 else NULL END) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
count(case when convert(int,isnull([Date Field],0)) = 1 then 1 else NULL END) as Jan,
count(case when convert(int,isnull([Date Field],0) ) = 2 then 1 else NULL END) as Feb,
count(case when convert(int,isnull([Date Field],0) ) = 3 then 1 else NULL END) as Mar,
count(case when convert(int,isnull([Date Field],0) ) = 4 then 1 else NULL END) as Apr,
count(case when convert(int,isnull([Date Field],0) ) = 5 then 1 else NULL END) as May,
count(case when convert(int,isnull([Date Field],0) ) = 6 then 1 else NULL END) as Jun,
count(case when convert(int,isnull([Date Field],0) ) = 7 then 1 else NULL END) as Jul,
count(case when convert(int,isnull([Date Field],0) ) = 8 then 1 else NULL END) as Aug,
count(case when convert(int,isnull([Date Field],0) ) = 9 then 1 else NULL END) as Sep,
count(case when convert(int,isnull([Date Field],0) ) = 10 then 1 else NULL END) as Oct,
count(case when convert(int,isnull([Date Field],0) ) = 11 then 1 else NULL END) as Nov,
count(case when convert(int,isnull([Date Field],0) ) = 12 then 1 else NULL END) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
Sum(case when convert(int,is0([Date Field],0)) = 1 then 1 else 0 END) as Jan,
Sum(case when convert(int,is0([Date Field],0) ) = 2 then 1 else 0 END) as Feb,
Sum(case when convert(int,is0([Date Field],0) ) = 3 then 1 else 0 END) as Mar,
Sum(case when convert(int,is0([Date Field],0) ) = 4 then 1 else 0 END) as Apr,
Sum(case when convert(int,is0([Date Field],0) ) = 5 then 1 else 0 END) as May,
Sum(case when convert(int,is0([Date Field],0) ) = 6 then 1 else 0 END) as Jun,
Sum(case when convert(int,is0([Date Field],0) ) = 7 then 1 else 0 END) as Jul,
Sum(case when convert(int,is0([Date Field],0) ) = 8 then 1 else 0 END) as Aug,
Sum(case when convert(int,is0([Date Field],0) ) = 9 then 1 else 0 END) as Sep,
Sum(case when convert(int,is0([Date Field],0) ) = 10 then 1 else 0 END) as Oct,
Sum(case when convert(int,is0([Date Field],0) ) = 11 then 1 else 0 END) as Nov,
Sum(case when convert(int,is0([Date Field],0) ) = 12 then 1 else 0 END) as Dec,
Sum(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
select
[Company],
count(case when month(convert(varchar,[Date Field],112)) = 1 then 1 else NULL END) as Jan,
count(case when month(convert(varchar,[Date Field],112)) = 2 then 1 else NULL END) as Feb,
count(case when month(convert(varchar,[Date Field],112)) = 3 then 1 else NULL END) as Mar,
count(case when month(convert(varchar,[Date Field],112)) = 4 then 1 else NULL END) as Apr,
count(case when month(convert(varchar,[Date Field],112)) = 5 then 1 else NULL END) as May,
count(case when month(convert(varchar,[Date Field],112)) = 6 then 1 else NULL END) as Jun,
count(case when month(convert(varchar,[Date Field],112)) = 7 then 1 else NULL END) as Jul,
count(case when month(convert(varchar,[Date Field],112)) = 8 then 1 else NULL END) as Aug,
count(case when month(convert(varchar,[Date Field],112)) = 9 then 1 else NULL END) as Sep,
count(case when month(convert(varchar,[Date Field],112)) = 10 then 1 else NULL END) as Oct,
count(case when month(convert(varchar,[Date Field],112)) = 11 then 1 else NULL END) as Nov,
count(case when month(convert(varchar,[Date Field],112)) = 12 then 1 else NULL END) as Dec,
Count(*) as TotalYear
from
MySourceTable
group by
[Company]
Order by
TotalYear
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
16 Experts available now in Live!