getting a count using datename... 1

[gtjr92]

I am trying to get a count of entries per month. The date created field is the field that holds the date of an entry.
When i run the below query it only gives me data from one day for each month, it does not iterate through the whole month. How can i have it so it will give me the total count from each month.

Select  
 distinct datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from blog_entry  
    WHERE blog_Entry.IsDeleted = 0 and 
  datename(Month,DateCreated)=datename(Month,DateCreated)
group by datecreated
order by monthid,year

 

[SQLDenis]

??? works for me
Have you taken into account that it is May first and you probably don't have data for May yet???????

create table #blog_entry (id int identity,DateCreated datetime,IsDeleted bit default 0)
insert into #blog_entry values(getdate(),0)
insert into #blog_entry values(getdate()+2,0)
insert into #blog_entry values(getdate()+3,0)
insert into #blog_entry values(getdate()+5,0)
insert into #blog_entry values(getdate()+7,0)
insert into #blog_entry values(getdate()+8,0)
insert into #blog_entry values(getdate()+9,0)
insert into #blog_entry values(getdate()+10,0)
insert into #blog_entry values(getdate()+11,0)


Select  
  datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from #blog_entry  
    WHERE #blog_entry.IsDeleted = 0 
and DateCreated >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+0, 0)
and DateCreated < DATEADD(mm, DATEDIFF(mm, 0, GETDATE())+1, 0) 
group by datepart(Month,DateCreated),
 datepart(year,DateCreated) ,datename(Month,DateCreated)
order by monthid,year

drop table #blog_entry

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> tried sqldenis too still i get no rows

Actually it should report an error... to clarify things, are you using Query Analyzer?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[gtjr92]

yes query anylzer.

"Have you taken into account that it is May first and you probably don't have data for May yet???????"
no because I am trying to get results for EACH month not just one month

like this

month     id    year    entry count
February  2	2006	1
April	  4	2006	1
June	  6	2005	1
July	  7	2005	3
September 9	2005	1

except that these counts are wrong as they are only for one day out of each month instead of the total count for every day for each month.

 

[SQLDenis]

mmmmm
why do you have a where claue that includes this month if you want more than this month?

run this for example

create table #blog_entry (id int identity,DateCreated datetime,IsDeleted bit default 0)
insert into #blog_entry values(getdate(),0)
insert into #blog_entry values(getdate()+2,0)
insert into #blog_entry values(getdate()+3,0)
insert into #blog_entry values(getdate()+5,0)
insert into #blog_entry values(getdate()+7,0)
insert into #blog_entry values(getdate()+8,0)
insert into #blog_entry values(getdate()+9,0)
insert into #blog_entry values(getdate()+10,0)
insert into #blog_entry values(getdate()+11,0)
insert into #blog_entry values(getdate()-2,0)
insert into #blog_entry values(getdate()-3,0)
insert into #blog_entry values(getdate()-5,0)
insert into #blog_entry values(getdate()-7,0)
insert into #blog_entry values(getdate()-8,0)
insert into #blog_entry values(getdate()-9,0)
insert into #blog_entry values(getdate()-10,0)
insert into #blog_entry values(getdate()-11,0)
insert into #blog_entry values(getdate()-32,0)
insert into #blog_entry values(getdate()-33,0)
insert into #blog_entry values(getdate()-35,0)
insert into #blog_entry values(getdate()-37,0)
insert into #blog_entry values(getdate()-38,0)
insert into #blog_entry values(getdate()-39,0)
insert into #blog_entry values(getdate()-30,0)
insert into #blog_entry values(getdate()-31,0)
insert into #blog_entry values(getdate()-10,0)
insert into #blog_entry values(getdate()-11,0)
insert into #blog_entry values(getdate()-82,0)
insert into #blog_entry values(getdate()-83,0)
insert into #blog_entry values(getdate()-85,0)
insert into #blog_entry values(getdate()-87,0)
insert into #blog_entry values(getdate()-88,0)
insert into #blog_entry values(getdate()-89,0)
insert into #blog_entry values(getdate()-80,0)
insert into #blog_entry values(getdate()-81,0)


Select  
  datename(Month,DateCreated)As Month,
 datepart(Month,DateCreated)As Monthid,
 datepart(year,DateCreated) As Year, 
 count(*) As EntryCount 
   from #blog_entry  
    WHERE #blog_entry.IsDeleted = 0 
group by datepart(Month,DateCreated),
 datepart(year,DateCreated) ,datename(Month,DateCreated)
order by year,Monthid

drop table #blog_entry

results

Month                          Monthid     Year        EntryCount  
------------------------------ ----------- ----------- ----------- 
February                       2           2006        8
March                          3           2006        7
April                          4           2006        11
May                            5           2006        9

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gtjr92]

that did it thanks denis.
"why do you have a where claue that includes this month if you want more than this month?"
I don't know i was trying so many different things that is the one that got me closes to the results. thanks again!!!

 

[SQLDenis]

No problem
That's why it's important to provide DDL and insert scripts, the answer would have been given at 1 May 06 8:05 in that case



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gtjr92]

that answer could have been given sooner if people would have just read my first post completley as i stated in it "I am trying to get a count of entries per month"" it does not iterate through the whole month. How can i have it so it will give me the total count from each month."

such is life.....
Thanks to all