I need to count by year and month and display results horizantally for more than ONE year.

Last solution posted worked great for 12 month period but I have 16 months of data so Janury 2009 and January 2010 are being summed togther when I need them seperated out by month AND year:

previous code posted that was used and worked well for what was originally asked.

In my code, the column 'ts' is time stamp, and the table test has that column.

select
sum(case when datepart(month,ts)= 1 then 1 else 0 end) as January,
sum(case when datepart(month,ts)= 2 then 1 else 0 end) as February,
sum(case when datepart(month,ts)= 3 then 1 else 0 end) as March,
sum(case when datepart(month,ts)= 4 then 1 else 0 end) as April,
sum(case when datepart(month,ts)= 5 then 1 else 0 end) as May,
sum(case when datepart(month,ts)= 6 then 1 else 0 end) as June,
sum(case when datepart(month,ts)= 7 then 1 else 0 end) as July,
sum(case when datepart(month,ts)= 8 then 1 else 0 end) as August,
sum(case when datepart(month,ts)= 9 then 1 else 0 end) as September,
sum(case when datepart(month,ts)= 10 then 1 else 0 end) as October,
sum(case when datepart(month,ts)= 11 then 1 else 0 end) as November,
sum(case when datepart(month,ts)= 12 then 1 else 0 end) as December
from
test
frogman22Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kenwagersCommented:
Simple change if you don't mind separate rows for each year - see the group by / order by below and I added Year to the columns.

select datepart(year,ts) as Year,
sum(case when datepart(month,ts)= 1 then 1 else 0 end) as January,
sum(case when datepart(month,ts)= 2 then 1 else 0 end) as February,
sum(case when datepart(month,ts)= 3 then 1 else 0 end) as March,
sum(case when datepart(month,ts)= 4 then 1 else 0 end) as April,
sum(case when datepart(month,ts)= 5 then 1 else 0 end) as May,
sum(case when datepart(month,ts)= 6 then 1 else 0 end) as June,
sum(case when datepart(month,ts)= 7 then 1 else 0 end) as July,
sum(case when datepart(month,ts)= 8 then 1 else 0 end) as August,
sum(case when datepart(month,ts)= 9 then 1 else 0 end) as September,
sum(case when datepart(month,ts)= 10 then 1 else 0 end) as October,
sum(case when datepart(month,ts)= 11 then 1 else 0 end) as November,
sum(case when datepart(month,ts)= 12 then 1 else 0 end) as December
from
test
group by datepart(year,ts)
order by datepart(year,ts)

If you need to keep moving the months out as columns, then the case might need to change to something like this:

sum(case when datepart(month,ts)= 1 and datepart(year,ts) = 2001 then 1 else 0 end) as January2001,

It's not going to be very workable if you continue to add additional years.

Another solution would be to go back to your original query, and then paste the results into Excel and Transpose it using Cut / Paste Special.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
select
sum(case when datepart(month,ts)= 1 then 1 else 0 end) as January,
sum(case when datepart(month,ts)= 2 then 1 else 0 end) as February,
sum(case when datepart(month,ts)= 3 then 1 else 0 end) as March,
sum(case when datepart(month,ts)= 4 then 1 else 0 end) as April,
sum(case when datepart(month,ts)= 5 then 1 else 0 end) as May,
sum(case when datepart(month,ts)= 6 then 1 else 0 end) as June,
sum(case when datepart(month,ts)= 7 then 1 else 0 end) as July,
sum(case when datepart(month,ts)= 8 then 1 else 0 end) as August,
sum(case when datepart(month,ts)= 9 then 1 else 0 end) as September,
sum(case when datepart(month,ts)= 10 then 1 else 0 end) as October,
sum(case when datepart(month,ts)= 11 then 1 else 0 end) as November,
sum(case when datepart(month,ts)= 12 then 1 else 0 end) as December
from
group by year(ts)
paddy_hCommented:
Hi,

The only way I can think of to do this is dynamically, as the months and years may be different each time you run it.

This script will build a temp table built on the dates in the test table, count how many entried for the month and year and populate the temp table.

I'm a bit tired to there is an odd [END] column in the temp table that has a zero in it - so I didn't have to think too hard about coping with a comma after each field!

It looks a bit messy as we are building the temp table within the synamic script, and have to populate it in there too, so we also select from it!

declare  @sql nvarchar(max)
set      @sql = ''

set      @sql = '

declare  @table nvarchar(max)
declare  @lastPeriod nvarchar(50)
declare  @insert nvarchar(max)
declare  @insert1 nvarchar(max)
set      @insert = ''insert into @@Temp1(''
set      @insert1 = ''values (''
set      @table = ''
declare  @@Temp1 TABLE(
''
set      @lastPeriod = ''''

while    (select MIN(Period)
         from     (
               select   case DATEPART(MM, ts)
                           when 1 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''January''
                           when 2 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''February''  
                           when 3 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''March''
                           when 4 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''April''
                           when 5 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''May''
                           when 6 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''June''
                           when 7 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''July''
                           when 8 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''August''
                           when 9 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''September''
                           when 10 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''October''
                           when 11 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''November''
                           when 12 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''December''
                        end as [Period]
               from     test) A
         where Period > @lastPeriod) is not null
begin
         set      @lastPeriod = (select MIN(Period)
         from     (
               select   case DATEPART(MM, ts)
                           when 1 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''January''
                           when 2 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''February''  
                           when 3 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''March''
                           when 4 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''April''
                           when 5 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''May''
                           when 6 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''June''
                           when 7 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''July''
                           when 8 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''August''
                           when 9 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''September''
                           when 10 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''October''
                           when 11 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''November''
                           when 12 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''December''
                        end as [Period]
               from     test) A
         where Period > @lastPeriod)
         set   @table = @table + ''['' + @lastPeriod + ''] nvarchar(10),
         ''
         set   @insert = @insert + ''['' + @lastPeriod + ''], ''
         set   @insert1 = @insert1 +
         --    do our count
         convert(varchar(5), (
         select   COUNT(ts)
         from     test
         where    (case DATEPART(MM, ts)
                                    when 1 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''January''
                                    when 2 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''February''  
                                    when 3 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''March''
                                    when 4 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''April''
                                    when 5 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''May''
                                    when 6 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''June''
                                    when 7 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''July''
                                    when 8 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''August''
                                    when 9 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''September''
                                    when 10 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''October''
                                    when 11 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''November''
                                    when 12 then convert(varchar(5), DATEPART(year, ts)) + '' '' + ''December''
                                 end) = @lastPeriod)) + '', ''
         
end
set      @table = @table + ''[END]varchar(1))''
set      @insert = @insert + ''[END])''
set      @insert1 = @insert1 + ''0)''
set      @insert = @insert + @insert1


set      @table = @table + ''
'' + @insert
set      @table = @table + ''
select   *
from     @@Temp1''
exec     (@table)
'
exec     (@sql)
frogman22Author Commented:
Thanks Paddy_H for all the work.  I will work the sample code today.

:-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.